SQLServerCentral / SQL Server 2005 / SQL Server 2005 Compact Edition

How i execute one of procedure Autmatically.

mandarcoolkarni31 — Thu, 05 Nov 2009 03:36:05 GMT

Hi All,How i execute one of procedure Autmatically with using [b]Sql server Schduler [/b]Because i want to execute the procedure every day on 6 a.m because of this procedure i want to update some records in table.So plz help me out.Waiting for positive responce.

sql 2k5 express or sql 2k5 mobile?

issmon44 — Wed, 04 Nov 2009 14:57:46 GMT

EDIT:Hello. Pc and mobile application. Need them to access the same online database. no browsers in either. Which sql edition should i install?Answer is sql 2k5 compact edition right?

Update Query

mandarcoolkarni31 — Wed, 04 Nov 2009 03:34:31 GMT

how to update all field in a table with same data without all column namebecause my table have 110 columnplz help me out..

How do I retrieve Foreign Key details?

relentless99 — Thu, 22 Oct 2009 13:30:54 GMT

How can I retrieve the table a Foreign Key references in Server CE?In SQL Server 2005 I can get the Constraint Name from the INFORMATION_SCHEMA.TABLE_CONSTRAINTS then lookup up that constraint name in the SYS.FOREIGN_KEY_COLUMNS. But Server CE does not have SYS.FOREIGN_KEY_COLUMNS.I need to display the table name my FK is referencing.

Install SQL 2005 for test

dvora.id — Mon, 12 Oct 2009 06:39:15 GMT

Hello,I know that for some of you this question it will sound stupid so I’m sorry, but is been a while that I’m trying to install sql server 2005 in my computer and when I finish the wizard installation the SQL doesn’t create the basic data bases.I’m working with sql server 2000 and I’m trying to install this in order to start checking this and upgrade the SQLDo I need to install a qa SQL only in windows 2003 ?thanks you very much!Debby

Visual Studio/ SQL Server Editions

Hafiz-1142834 — Mon, 05 Oct 2009 08:51:52 GMT

HelloCan someone breif me the cheap and best editions [[ Front End and Back end ]] to develop (and host) a simple webiste. I have the follwing requirements1. Need to host a website with some hundreds of Images in Win XP pro IIS. These images I am planning to store in a folder and not in a database and iterate thru XML. Need be able to Use rich GUI controls. I am expecting answers like1. MS visual Studio 2008 Full edition -- Trail Software + SQL Server 2008 Express Edition2. MS web developer Free Edition + SQL Server 2008 Express Editionetc.Please help me here. Thanks in advance. I am ready to post more replies in case I am not clear enough for your decisionsThanks

Build embedded database applications for desktops and mobile devices

babukaka2009 — Tue, 29 Sep 2009 11:48:27 GMT

SQL Server Compact 3.5 is a free, easy-to-use embedded database engine that lets developers build robust Windows Desktop and mobile applications that run on all Windows platforms including Windows XP, Vista, Pocket PC, and Smartphone.thanking youkaka-----------------[url=http://www.intelligenceverte.org/]week end cuisine[/url]

SQL Compact DB installation in End user machine.

sethu madhavan-456892 — Mon, 21 Sep 2009 09:34:13 GMT

Hi,I have create a application using SQL Compact DB (sample.sdf). The issue is during installation in the End User box, do i need to install the SQL Express editon or SQL Server Compact.thanksSethu

sql express 2005 install with sql server 200

tsimek — Wed, 16 Sep 2009 07:28:27 GMT

Can someone tell me if I can install sql express 2005 on a server along side of sql server 2000? The server hosts websites for our Intranet and I want to utilize the aspnetmdb that is created for log in and passwords. The website works fine in development but when I deploy to the server the error indicates that sql express can't be found. I'm hoping that simply installing sql express on the server is the answer. Any thoughts?

Please Help

twsexela — Mon, 07 Sep 2009 15:21:51 GMT

You are the junior DBA in a large company. A particular table in your database using an almost full tablespace with multiple small data files has been identified as needing reorganization and re-location to another set of disks. (The data alone require at minimum the space of three disks).Your superior DBA has decided that this has to be done by extracting all data of the table to a sequential set of files on tapes, dropping the current table, recreating it on the new set of disks, and then re-loading the data after properly sorting them. An application programmer will do the tasks of unloading, sorting and reloading the data – that is not your responsibility.Your task is to drop and recreate the table (and tablespace) on the new set of disks in such a way that after reloading the data, the users will not be impacted in any way when using the table and its data and the new table will provide for 50% free space for future additions to the data.Explore what information from the existing table and from the catalog you can use, and describe step-by-step what you as DBA would have to do to re-create the table according to specification. Hint: Which objects in the catalog (DDL) relate to or are dependent on a table?Condition: You can use SQL and DDL, but not an integrated database management tool like the Oracle Enterprise Manager.

please help me

dheerajbaghel — Wed, 02 Sep 2009 08:19:14 GMT

Hello everyonei m a beginner in sql i want to know thathow to insert a table column as the parameter of a user defined function.thanks

Can you use sql profiler with Sql Compact Edition

hassan khan-471985 — Thu, 27 Aug 2009 16:24:13 GMT

I read somewhere that sql compact edition does not have hooks to support profiler. Is that true ? Does it mean there is no way to profiler Sql CE based dbs ?

can get CE working with SQL Server 2005 and VS 2008

ericvanderhousen — Thu, 27 Aug 2009 05:45:45 GMT

Hi,I was having problems with compatibility, so decided to unistall-reinstall, now, I can't get VS2008 to load an sdf file.pls help!I have installed:VS2008SQL Server 2005SQL Server 2005 CE SDK2005 ME SDKSQL Compact 3.5 SP1SQL Compact 3.5 SP1 server tools(looking at Control Panel\Programs and Features )im running Vista

How to create an Excel file using SQL 2005

Dhiren Shah-425130 — Fri, 21 Aug 2009 00:21:30 GMT

Please help me to create dynamic XLS file and insert data in different sheet.

merge replication extremely slow in VS.NET 2005 / CF 2.0

Gary Watson-478259 — Tue, 19 Aug 2008 10:31:39 GMT

Hello all,I am in charge of upgrading a .NET CE application from running on SQL Server 2000 to SQL Server 2005. The code was originally started in VS .NET 2003 with Compact Framework 1.1, but I have upgraded all references to CF 2.0 and SQL CE 3.0.I have had to recreate the mobile publication on SQL Server, I did so using the wizard, ensuring that it was for SQL Server 2005 Mobile, and have included all of the necessary tables. 2 tables are filtered using the SUSER_SNAME() function, and these then filter down further to other tables, joined via unique IDs. Aside from the type of publication, I believe that I have reproduced our desktop publication accurately.Now, I use the following code inside a thread in .NET on a Pocket PC device in order to create a subscription and a database on the device:Dim repl As SqlCeReplication = Nothingrepl = New SqlCeReplicationrepl.InternetUrl = SyncSet.InternetURLrepl.InternetLogin = SyncSet.InternetIDrepl.InternetPassword = Registry.Decrypt(SyncSet.InternetPassword)repl.PublisherNetwork = [b]Data.SqlServerCe.NetworkType.DefaultNetwork[/b]repl.Publisher = SyncSet.Publisherrepl.PublisherDatabase = SyncSet.Databaserepl.Publication = SyncSet.Publicationrepl.PublisherSecurityMode = [b]Data.SqlServerCe.SecurityType.DBAuthentication[/b]repl.PublisherLogin = SyncSet.UserIDrepl.PublisherPassword = Registry.Decrypt(SyncSet.Password)strDataSource = MyConnection.DataSourcerepl.SubscriberConnectionString = "Data Source=" & strDataSource & ";Password=password"repl.Subscriber = SyncSet.Subscriptionrepl.HostName = repl.Subscriberrepl.AddSubscription([b]AddOption.CreateDatabase[/b])repl.Synchronize()The SyncSet and Registry variables refer to internal structures holding various properties such as our database name, etc. I have highlighted the ones that I believe to be relevant in bold.This only seems to access the server for about 1-2 mins, at most. The device is then taking over 5 hours to transfer approximately 36000 rows into approximately 75 tables on the database held in the Pocket PC device, some containing more data than others, but none more than 2-3K rows each. Before the upgrade, this same code would take approximately 30-40 minutes to transfer the same amount of data. I am at my wits' end trying to work out what has changed so drastically.After various paths of investigation, it would seem that SQL Server 2005 Mobile now applies foreign keys, where it didn't seem to do so in the SQL Server 2000 mobile edition. Also, looking at the SQL Server 2005 snapshot files, there are now a whole lot of files with a PRC extension that didn't exist in the previous 2K version.I have tried amending the article options, these have been set for all tables as follows (items in bold are ghosted, and cannot be edited). As many have been set to False as possible, any more seems to cause replication to fail after only a minute or two:Copy primary key constraint: TrueCopy foreign key constraints: FalseCopy check constraints: FalseCopy clustered index: TrueCopy nonclustered indexes: TrueCopy default value specifications: FalseCopy user triggers: FalseCopy extended properties: FalseCopy collation: FalseCopy unique key constraints: FalseDestination object owner: dboAction if name is in use: Drop existing object and create a new oneSynchonization direction: BidirectionalPartition options: OverlappingCreate schemas at Subscriber: TrueConvert XML to NTEXT: FalseConvert MAX data types to NTEXT and IMAGE: FalseTracking level: Row-level trackingVerify INSERT permission: FalseVerify UPDATE permission: FalseVerify DELETE permission: FalseMulticolumn UPDATE: [b]True[/b]Subscription options are as follows (items in bold are ghosted, and cannot be edited):Report conflicts centrally: [b]True[/b]Allow anonymous subscriptions: TrueAttachable subscription database: FalseAllow pull subscriptions: TrueAllow Parameterized filters: [b]True[/b]Validate Subscribers: SUSER_SNAME()Precompute partitions: TrueOptimize synchronization: [b]False[/b]Limit concurrent processes: FalseMaximum concurrent processes: [b]0[/b]Replicate schema changes: FalseHas anybody had any similar experiences to these problems? I would dearly love to know if this is a documented bug (have not found any evidence so far), or of any workarounds that will improve the performance.Kind regards,Gary

Export SQL2005 to Compact Edition 3.5

Santiago E. Perez — Tue, 19 May 2009 14:11:47 GMT

Hello, new to COmpact Edition. I need to basically replicate all objects including table structures, FK Constraints, views, procs etc. that I have in SQL 2005 database to a CE 3.5 Database. What's the best way of doing this? I tried running scripts but got all kinds of errors.

Connect to SQL Server Compact Edition with OLE DB

kf-603404 — Fri, 21 Nov 2008 05:42:31 GMT

Beautiful day!I would like to ask somebody to help me.I have the following problem:I have a Windows Mobile Device with SQL Server Compact Edition 3.5 and an sdf file in Program Files. I also have a desktop PC with SQL Server 2008. I would like to make an application not with .NET framework. I should connect to the sdf file with OLE DB. I set the connection string as follows:Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=Mobile Device\Program Files\database.sdf;pwd=pwdI tried it with OLE DB in CodeGear RAD Studio and in Visual Studio too. In both environment I got the following exception at Open function:Multiple-step OLE DB operation generated errors...What can I do? If I try to connect a local sdf file on desktop pc I get the same message.Please somebody help me!

Oledb parameterized query sqlserver ce does not support?

sujithapril — Wed, 11 Feb 2009 00:07:50 GMT

OleDbConnection con = new OleDbConnection(constring); con.Open(); string qry2 = "select * from [dbo.TblCategory] where Name=?"; OleDbCommand cmd = new OleDbCommand(qry2, con); System.Data.OleDb.OleDbParameter p = new OleDbParameter("@p1", OleDbType.VarWChar);p.Value="demo"; cmd.Parameters.Add(p); OleDbDataReader rdr = cmd.ExecuteReader();It throws error type is not supported.when i use SqlCeCommand ,error does not occurs. i need it to work with Oledb anuy idea?i use sqlserver 2005 compact edition.c#.net 2.0

Size Of a File

sgovindakrishnan — Thu, 06 Aug 2009 04:16:32 GMT

I want to find the size of some files using SQL like (DOC,JPEG)...how to do it...

SQL Compact Edition (Formally Mobile?)

Alasdair Thomson — Wed, 05 Aug 2009 03:48:47 GMT

Hi all, I hope you are all well...So what exactly does SQL 2005 Compact edition do (I have read the stuff on books online) but want a real life example/description of what it does and does well...You see I am interested in whether I can create reports which can run on a mobile browser (windows mobile). I have created an mobile data base, but can see that I am unable to directly open the table I have created and so unable to add data so can i use an SSIS package to update the table and then run a SSRS report etc.Any decent examples pages, books etc would be greatly appreciated.Thank you in advance...

About multiUser support in SQL Compact 3.1.

bhakti-795560 — Fri, 26 Jun 2009 22:51:32 GMT

Hello,I am using SQL Compact Edition database 3.1 .Database is on Server.I want the same database to be accessed by multiple Users.Does SQL Compact Edition 3.1 support mulitple users database access?If yes then how can we allow doing such facility?When more than one user open connecion at same time it gives error "File Violation Error."How can I solve this problem?

Filter Replicated Data???

kjoyce — Thu, 18 Jun 2009 07:42:26 GMT

I've developed a vb.net mobile application that replicates data back to sql server 2005. I now need to program a filter for pulling the replicated data. I can use HOST_NAME() to implement a single parameter filter, for example, RegionCode = '1111'. I can set HOST_NAME() = '1111' in my mobile application and this works when I synchronize the data. My problem is I need to pull multiple RegionCodes and can't figure out how to set this filter. For example, I need to filter RegionCode = '1111' or RegionCode = '2222'. It doesn't work when I set HOST_NAME() = '"1111", "2222"'. Can anyone be of assistance?

insert into

brennendeliebe05 — Fri, 05 Jun 2009 11:41:25 GMT

Hi,I must to check a code from a friend bcoz he cant insert a new row into a table, the row code that generate the erros is:INSERT INTO [Spirit].[dbo].[DimTime]the erros says: spirit.dbo.dimtime is not validheeeeeeelpCyn

SQLCMD Switches (commands)

Sohrab — Thu, 04 Jun 2009 06:04:49 GMT

Could someone please post an example how to use the switches? For example how to use –n to eliminate header of a query result.Thanks.

Schema change in client db

Pratibha_Pillai — Wed, 27 May 2009 06:28:00 GMT

Hi,I came across default trace offered by sql server 2005.1)Can it act as a substitute to DDL triggers?What's the difference between them?Also I didn't find any column in the log file where i can get the sql command which was fired last.2) Also can anyone give me a brief idea as to what is the pupose of scripting(wherein we use the wizard to script different objects) is it also a way to track database schema changes?Actually I am in a way to find the best way by means if which i can track the schema changes on server db and notify the same to the client(sql ce).3)How can i propagate the schema changes from server to the client(mobile) and are the sql command compatible to sql ce commands?Thanks in advance.

Import from Excel? Maybe DTS in SQL Server CE?

hypermommy — Tue, 12 May 2009 07:54:05 GMT

I need to figure out a way to use an Excel file to determine the data structure and data for a SQL CE Database. Ideally, I'd be able to just suck it all at one time becuase building the table and iterating through the Excel one row at a time is painfully slow. I'd heard SQL Server CE had data transformation services. Is that true? If so, could I use it to do this? If not, how can I do this? Thanks!

About Operating the SQL server 2005 Compact Edition

bhakti-795560 — Fri, 29 May 2009 01:00:49 GMT

I have the sql server 2005 Management studio while logging to it Connect to Server dialog comes - it having Server Type Block. By default its having Database Engine, I want to switch to Sql Server 2005 Compact Edition,but the drop down is Enabled false. I download and installed the sql server 2005 Compact edition again but still cannot operate it why?Please help

Applying SQL 2005 Service pack 3 on a replication publisher for SQL CE 3.0 subscribers

IlanP — Thu, 28 May 2009 20:31:01 GMT

I have a SQL 2005 SP2 installation which is involved in replication (as the publisher) with SQL CE 3.0 clients (subscribers) in a merge replication senario. I want to upgrade the main SQL Server to SP3, and need to ensure that it's not going to have a negative effects on the Apps using SQL CE, or require any upgrade on the SQL CE clients. Any help or insight would be appreciated.

import table and data in compact edition

am-244616 — Tue, 26 Jun 2007 13:53:00 GMT

Guys,Is there anyway we can import data into sql server 2005 compact edition.Any inputs/suggestions would helpThanks

Web synchronization merge via proxy server

rapmapper — Mon, 11 May 2009 01:02:14 GMT

Dear All,I have successfully configured merge replication via web synchronization.We are hosting the 2005 server. I have tested to a remote web server that's not part of our domain and successfully subscribed and synchonised to a SQL Mobile\Compact Edition.The trouble I'm having is when trying to perform the same exercise where the subscriber is communicating via a proxy server.I'm getting the classic[color=FF0000]The schema script '\\MyServer\ReplData\unc\MySever_MyDatabase_MyPublication\20090508141794\Class_2.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147024891)The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)[/color]Does anybody know if ports other than port 80 are used at the client end to receive the data? I can't find any documentation describing the paths the data travels along.I should clarify, Replication monitor shows the server is receiving the request it just seems to be unable to provide the response.regardsAndrew

Mobile Server Edition

aarjunjadhav-1084029 — Wed, 06 May 2009 01:50:15 GMT

I have create database using SQL Mobile Server.And develop a mobile application using VB.NET 2008.I want to connect the .sdf database file to mobile application.I am trying to connect the file with following steps:Add New Data Source-->But i cant see Microsoft SQL Mobile Server,In Data Source table,can you help me.

Subselect problem in SQLCE!!!

Fockaert — Thu, 05 Feb 2009 02:48:33 GMT

The following code must return a row number of the selected Project, the id2 (Parameter) is the ID of the selected Project.The query is working correct under SQL Server 2005 but not on the compact version.I get this error:[code]There was an error parsing the query. [ Token line number = 1,Token line offset = 10,Token in error = SELECT ][/code]Can someone help me find a correct solution.[code]SELECT ( SELECT COUNT (*) FROM ( SELECT Project.id AS id , Project.name AS name FROM Project WHERE (Project.id = @id OR @id IS NULL ) AND ( Project.name LIKE '%'+ @name + '%' OR @name IS NULL ) ) Temp1 WHERE ((Temp2.name > Temp1.name)) ) AS SORT_ORDER FROM (SELECT Project.id AS id , Project.name AS name FROM Project WHERE ( Project.id = @id OR @id IS NULL ) AND ( Project.name LIKE '%'+ @name + '%' OR @name IS NULL ) ) Temp2 WHERE (Temp2.id = @id2) [/code]

Error: "Too many SqlCeParameters specified"

Girish Bhat — Fri, 24 Apr 2009 00:17:05 GMT

I need to update 60 fields in a table and use 4 fields in the WHERE clause. In order to reduce query compilation cycles, I tried to use SqlCeCommand with parameters and prepared query. When I issue the Prepare() method, I get an error "Too many SqlCeParameters specified in the currently executed query.". I have matched the number of parameters with the placeholders in the query. The query goes through with up to 25 parameters but not more than that. Has anyone successfully resolved this error? Please let me know.Thanks,Girish

.....Learning...SQL....Please help

El Masry — Wed, 22 Aug 2007 22:53:00 GMT

I would like to learn, but I know that the best way for me to master it, is to have the software with the book / manual infront of me, and keep practicing.

I can not afford to go to school, time wise and financialy

Is there a book tutorial with CD kind of thing to learn?

Your help is greatly appreciated.

datetime - 1/1/1900 treated as blank??

d short — Fri, 17 Apr 2009 12:49:27 GMT

I knew that a datetime field defaulted to '1/1/1900' when the field contains blanks, but it's looking like the 'inverse' is also true, which is causing problems. that is, when I [b]specifically insert a value of 1/1/1900[/b] into a datetime (or smalldatetime) field, it's treated as blank. I figure I must be missing something, but consider the following:create table testdate2 (datefield smalldatetime,thekey int identity(1,1),descr varchar(20))insert testdate2 (datefield,descr) values('','blank')insert testdate2 (datefield,descr) values('1/1/1900','1/1/1900')insert testdate2 ( descr) values('null')select * from testdate2select * from testdate2 where datefield is null -- OKselect * from testdate2 where datefield ='1/1/1900' -- OKselect * from testdate2 where datefield ='' -- no, one of them really should have '1/1/1900'select * from testdate2 where datefield ='' and datefield ='1/1/1900' -- ?Like I said, I must be missing something because I can't find this problem addressed anywhere. tia.....

SQL Server CE questions

mmoklak — Thu, 02 Apr 2009 07:52:38 GMT

Hello,I apologize in advance, but I am running into a performance issue on a PPC app that uses CE 2.0. The SDF exists on a card, and the app accesses the DB. On one particular lookup, well, not a look up really, I have an ID of a record, and I now want to pull that record from the table. The table is not what I would call well formed, nor can I make wholesale changes. (I have inherited this app and it's quirks) Back to the question. Using one clients SDF the retrieval is fast, less than a second. Same sdf structure, same code, same machine, but a different clients SDF (approx. same # of records, within 10%) the retreival can take upwards of 10-15 seconds. The only "data" difference is a blob field in the table. An image field, stored in record (not my choice) that the slower client populates, while the faster client does not. Tried removing that field from the table, and no appreciable difference. The indexes on this table are a little quirky I think, and it currently had no primary key. Indexes as follows:8 non unique, non clustered indexes using the following fields:dob_lastname_fname_mname dob is datetime, other 3 are varchar 50s, null alloweddob datetime, null allowedfname varchar50 nulliaddresseid int null allowedivoterid (do not let the name fool you) it is a varchar 50 field, not nulllastname, fname, mname, varchar 50s nulllastname fname dob and one last varchar look up field. My concern is the number and type of indexes, especially on a table with no primary key. I am nto that adept in CE, but I think these indexes might be hurting performance on the table. Thank you

Paging

mashtal_et_1982 — Thu, 19 Feb 2009 04:28:01 GMT

Hello, I have a product table in my database I want to display all products (300000) in my windows application but 10 after 10 ,How can I do that in the select statement?

ADD A COLUMN TO A TABLE IN T-SQL

Harry Drenth — Mon, 23 Feb 2009 03:13:02 GMT

I 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 Drenth

need help for trigger sql

swanand.lord — Fri, 27 Mar 2009 23:27:55 GMT

hi frendsi m doing proj on air traffic controli hav a table likeplane_position(Flight_no varchar, DateTime datetime, Longitude float, Latitude float, Altitude float)condition is i wanna create a trigger which will chek following conditions while insertimg a new record 1. There are at the two records present at a time corresponding to any Flight_no 2. While adding a record for some flight_no, the oldest of the two records already present is deleted according to value of datetime field 3. if less then 2 records for that flight_no are present then then add directlyplz help me frends, its urgent

need help for trigger sql

swanand.lord — Fri, 27 Mar 2009 23:29:40 GMT