Features In SQLCE 3.5 BETA 2: TOP, DISTINCT, SUB QUERIES?
Sep 15, 2007
Are these implemented in Beta 2:
TOP
DISTINCT
sub queries in SELECT clause?
sub queries in FROM clause? (as in ... FROM (SELECT ...)
ADVERTISEMENT
DB2 Viper 2 Beta - Compatibility Features, Need Your Help
Jun 15, 2007Hi folks,My fellow team mates had some extra time on their hands so we decided tospice up DB2 with a grab-bag of compatibility features.We wouldn't mind help validating the semantics match....So for those of you blessed or cursed with a competitive DBMS here is alist of what we've added:1. ** DUALAlways works without prefixing a schema2. ** ROWNUM3. ** (+) outer join syntax4. LEAST/GREATEST/NVL/DECODE5. TO_DATE/TO_CHAR improvementDB2 supports most common patterns except those requiring languageawareness6. ** CONNECT BYThis is a function drop, performance drop will follow7. A slew of syntactic sugar like:Seq.NEXTVAL and seq.CURRVAL notationUNIQUE instead of DISTINCTMINUS instead of EXCEPTUnnamed nested subqueries (aka inline views)"SELECT * FROM (SELECT * FROM T)"CROSSJOIN8. BITAND/BITOR/.....The features marked with ** require a registry setting:db2set DB2_COMPATIBILITY_VECTOR=3Fshould switch everything on.There are other features those porting apps will value:GLOBAL VARIABLESA new ARRAY data typeA new RID() function can be used to map ROWIDDocs: https://publib.boulder.ibm.com/info.../v9r5/index.jspEnjoySerge--Serge RielauDB2 Solutions DevelopmentIBM Toronto Lab
View 2 Replies View RelatedUnable To Execute Below Mentioned Queries Against SqlCe 3.1
Apr 10, 2007Hi,
When I run the following queries they are working fine with MS Access but not working with SqlCe 3.1 on my desk top.
I am using Sql Server 2005 management studio to connect to SqlCe (.sdf) file and running the queries with in that.
Please guide me what is wrong here:
1)
select count(*) FROM (SELECT DISTINCT TIDVALUE FROM cdirmen)
Error Message:
Major Error 0x80040E14, Minor Error 25501
> select count(*) FROM (SELECT DISTINCT TIDVALUE FROM cdirmen)
There was an error parsing the query. [ Token line number = 1,Token line offset = 23,Token in error = SELECT ]
2)
SELECT distinct tidvalue FROM cdirmen WHERE objname='I_MAP' AND menuid<>0
AND 0<(SELECT COUNT(*) FROM cmenu WHERE cmenu.menuid=cdirmen.menuid)
AND 0=(SELECT COUNT(*) FROM cmenu WHERE cmenu.menuid=cdirmen.menuid AND cmenu.item like 'CL%')
Error Message:
Major Error 0x80040E14, Minor Error 25501
> SELECT distinct tidvalue FROM cdirmen WHERE objname='I_MAP' AND menuid<>0
AND 0<(SELECT COUNT(*) FROM cmenu WHERE cmenu.menuid=cdirmen.menuid)
AND 0=(SELECT COUNT(*) FROM cmenu WHERE cmenu.menuid=cdirmen.menuid AND cmenu.item like 'CL%')
There was an error parsing the query. [ Token line number = 2,Token line offset = 8,Token in error = SELECT ]
Many DISTINCT Queries
Jul 20, 2005Hi,I have a table that contains log data, usually around a million records. Thetable has about 10 columns with various attributes of the logged data,nothing special. We're using SQL Server 2000.Some of the columns (for example "category") have duplicate valuesthroughout the records. We have a web page that queries the table to showall the unique columns, for example:select distinct CATEGORY from table TESTObviously the server has to scan all rows in order to get all unique columnswhich takes quite a while, especially since that web page contains severalof these types of queries. We also have a MAX(DATE) and MIN(DATE) query thatalso add to the load.I already created indexes on the CATEGORY (actually on all categories)column which might help a little but I'm pretty sure that there has got tobe a better way.I also create a view (select distinct CATEGORY from table TEST) and tried toindex it, but it won't let me index a query that contains a DISTINCTstatement.Isn't there a way to create an index that contains only the distinct values?Is there another way to speed this up?Thanks for any hints!
View 4 Replies View RelatedMS Sql 2005 Beta 3 Installation Errors..Can This Version Coexist With VS2005 Beta 2
Apr 27, 2005When i tried to install the beta 3 above I encounter the following problems ;-
TITLE: Microsoft SQL Server 2005 Beta 3 Setup----------------------------------------
Setup configuration check cannot be executed due to WMI configuration on the computer D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnetwcls.mof. For details, see the Sqlrunxx.log file or call Microsoft Product Support.
A seach in the net has the followings help link
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&ProdVer=9.00.852&EvtSrc=setup.rll&EvtID=29535&EvtType=sqlca%5Csqlmofca.cpp@Do_sqlMofcomp@Do_sqlMofcomp@x8007735f
Can this version coexist with VS2005 beta 2 because before i can install visual studio 2005 beta 2 I need to uninstall the dot net framework 2.0 beta which come together with the beta 3 sql 2005 installation.
Hey I link this editor, Where can I get this beautiful forum message editor ?.
When Does Ssrs Use A DISTINCT Clause In It's Queries?
Jun 27, 2007I am having a heck of a time figuring out what controls how/when the generated SQL for a report puts a DISTINCT clause in front of it.
For instance, not that this report makes any sense, but I have 58 rows in my fact table/entity €“ If I pull in a lookup field and execute, the distinct is put in the query and I basically get a list of the possible domain values. It runs the whole joined table query to get them, but it does list (in this case) just 4 records. Now I put in the primary ID of the fact entity and the distinct goes away and I get my 58 rows. If I put two lookup fields, the DISTINCT is back. If I pull in the description field (text string just a direct source field mapping not part of the identifying attributes), the distinct is there. If I pull in the Company Name field on a different entity (which is essentially the same as pulling in Description only it is part of the identifying attributes), there is no DISTINCT. I can pull in all my fields on this entity and none of them drive the distinct. And I swear (ok, I am probably lying but not on purpose) the field/attribute and roles properties are all the same on the attributes. But you get my general question/situation...
Any insight for me? Does it have to do with how I am building the report rather than the underlying model?
Thanks in advance,
Using Multiple Distinct Queries Inside Single Stored Procedure
Feb 21, 2008Hello,
I was wondering if anyone can explain the positives and negatives of using a single stored procedure that contains one or more distinct queries. I know there are problems with dynamic SQL but I am not proficient enough to know whether this falls under that umbrella.
For clarification, what I am referring to is this: In a single stored procedure, I have a parameter called Query_ID that is used to identify which query in the sproc that I want to execute. Then from my ASP page, I simply pass the appropriate value for Query_ID. So:
IF @QUERY_ID = 1
BEGIN
SELECT [whatever]
FROM [tbl1]
WHERE [conditions]
GROUP BY [something]
ORDER BY [somethingelse]
END
ELSE
IF @QUERY_ID = 2
BEGIN
SELECT [whatever]
FROM [tbl2]
WHERE [conditions]
GROUP BY [something]
ORDER BY [somethingelse]
END
END
I hope that makes sense. Thanks in advance.
Sqlce 3.0 Vs Sqlce 2.0 Memory Issue
Feb 6, 2006I am currently upgrading our ppc app (written in .net 2003) to .net 2005 and from sqlce 2.0 to 3.0. The new application runs out memory(storage) when handling lots of data transactions (both in case of using sqlcedatareader, and dataset ). There is no memory leak issue here, sqlce 3.0 simply uses a lot more (3 times more) memory than sqlce 2.0.
Our applicaton runs fine using sqlce 2.0 and .net 2003, but fail due to memory shortage
with the upgrades(which has the same code). Anyone can shed some lights on this issue?
How Do You Use The Upgrade.exe For Sqlce 2.0 To Sqlce 3.0?
Jun 9, 2006Does anyone have any instructions on how to go about uprading a .sdf file
from sqlce 2.0 to sqlce 3.0?
I found this on an msdn website
(link: http://www.microsoft.com/sql/editions/sqlmobile/upgrading.mspx) but
as you can see its very lacking on detail. (I included the link and content
referring to upgrading the database files so you won't have to go to the link
to see the part referring to upgrading the database.) I tried to run the upgrade.exe on my device but it doesn't explain how you're supposed to point it to the .sdf that you are trying to update. Here is the content on the above web page.
Upgrading Database Files
You can upgrade a database created with an earlier version of SQL Server CE
2.0 to a database created with SQL Server Mobile by using the SQL Server
Mobile Database Upgrade tool (upgrade.exe). The Database Upgrade tool runs on
a smart device.
When you run the SQL Server Mobile Database Upgrade tool, the new SQL Server
Mobile database is created on the smart device. The new database, with the
file name extension .sdf, contains all the data that was in the source
database. You need to reinitialize the upgraded database to continue using it
for replication.
Note: If you are using replication or remote data access (RDA) as a
connectivity solution before upgrading to SQL Server Mobile, you must
synchronize the source SQL Server CE database (subscription database) with
the SQL Server database (Publisher). The reason for synchronizing before
upgrading to SQL Server Mobile is to ensure that any changes that exist in
the tables on the SQL Server CE database are updated on the SQL Server
database, because after the upgrade you must resubscribe or repull using the
new database.
SQLCE 2.0 And SQLCE 3.0, Both On Same Server
Oct 11, 2006I presently run on my server SQL SERVER CE 2.0 and want to install, in addition on SQL Server 2.0, SQL Server Mobile 2005.
Is there a procedure to have both installed and running on a computer?
Thanks!
Marco
Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?
Jul 6, 2007Hi, I have the following script segment which is failing:
CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))
INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache
When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."
Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.
The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.
Any ideas?
Trying To Add A NON-DISTINCT Field To A DISTINCT Record Set In A Query.
Mar 12, 2007I need to run a SELECT DISTINCT query acrossmultiple fields, but I need to add another field that is NON-DISTINCTto my record set.Here is my query:SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, genderFROM gpresultsWHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis query runs perfect. No problems whatsoever. However, I need toalso include another field called "admitdate" that should be treatedas NON-DISTINCT. How do I add this in to the query?I've tried this but doesn't work:SELECT admitdateFROM (SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, gender from gpresults)WHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis has to be simple but I do not know the syntax to accomplishthis.Thanks
View 2 Replies View RelatedFeatures Of Sql
Jan 10, 2008hi,
how i know the extra features added in the sql2005 than sql 2000
SQL 2000 Features
Mar 8, 2000Does SQL 2000 support nested tables? Also, how many columns does it support?
View 3 Replies View RelatedDiscontinued Features ???
Sep 14, 1998Are there any features in 6.5 that have been discontinued in 7.0? I read in Technet that "future versions of SQL Server might not support some statements where the same functionality can be achieved using other means." Technet gave the following examples:
- Device Mirroring
- Outer Join Syntax
- SELECT FOR BROWSE
- Handling of NULLs
I`m curious how our applications will be effected. Will we be making a lot of code changes when we migrate to 7.0?
Thanks, Dave Holtz
First Penn-Pacific
New Features And Comparison.
Aug 13, 2004Morning!
Folks, i want some links where i could find healthy stuff regarding New Features and Compliance levels, performance comparisons, TPC tests etc about SQL 2000 and Yukon specially.
I've to submit a document regarding Top database features that shall be used for a Medical-Billing Software in plan.
Howdy!
Does SSIS Have These Features????
Feb 20, 20061> Support for rules based and change-data-capture extracts
2> Push and Pull methods supported
3> Convert between ASCII and EBCDIC
4> Ability to extract from packages like SAP and Peoplesoft natively
5> Extract from multiple sources simultaneously
6> Support for native database features like database bulk loaders
DISTINCT To ShortDateString, Not DISTINCT To The DateTime; How?
May 8, 2006Hello,
I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a tableCREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime )
This app also calculates the total number of proposals coming from a specific branch in a given date bySELECTing COUNT(BranchID) FROM Proposals WHERE BranchID=@prmBranchID AND Proposal_Date=@prmDateand prints them in a table (my target table).
This target table has as many rows as the result of the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the"SELECT DISTINCT BranchID FROM Proposals". This target table converts the DateTime values ToShortDateString so that we are able to see comfortably which branch office has sent how many proposals in a given day.
So far so good, and everything works fine except one thing:
Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and11.11.2005 10:45:30) cause some trouble in the target table, where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows)
What I need to do is to get a result like (in a neat fashion :)
"SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals"
So, how to do it in a suitable way?
Thanks in advance.
SQL Server Security Features
Jul 2, 2003Hi,
Can any one give me the details of the Security level or measure which are supported my MS Sql Server.
How much it is security while we are accessing any database over the internet and what are the Security measure we have to take and level did Software provides.
It is URGENT
Thanks
Advanced Features For DTSs?
Apr 17, 2006Hi,
Since I am new to DTSs therefore currently I am making my DTSs in a manner where there probably is a room for improvement e.g. When I have to Import data from one server to another I use the Import feature and then I go through all the steps of the wizard (at the end of which I get the option to save my DTS)....
In the DTS "design window" there are many options under the TASK menu option and is there a url from where I can learn how to use these options (under TASK i.e. File Transfer Protocaol task, ActiveX Script Task, Transform Data Task, etc. etc.).
Maybe if I know how to use these options, I might be able to write my DTS in better and flexible manner...
Hope my posts is making some sense????
Thanks
Advanced Features For DTSs?
Apr 17, 2006Hi,
Since I am new to DTSs therefore currently I am making my DTSs in a manner where there probably is a room for improvement e.g. When I have to Import data from one server to another I use the Import feature and then I go through all the steps of the wizard (at the end of which I get the option to save my DTS)....
In the DTS "design window" there are many options under the TASK menu option and is there a url from where I can learn how to use these options (under TASK i.e. File Transfer Protocaol task, ActiveX Script Task, Transform Data Task, etc. etc.).
Maybe if I know how to use these options, I might be able to write my DTS in better and flexible manner...
Hope my posts is making some sense????
Thanks
Sql Server 2000 New Features ?
Jan 28, 2004Please help ,
What are the major differences between SQL SERver 7 and 2000 ?
Looking for a job and found a position where the client prefers the database developer specifically expericenced in SQL 2000.
What is really so hot and important to know?
THanks a lot .
Transact-SQL Common Features?????
Jan 12, 2007Hi
The white paper which compares SQL Express and SQL Compact says that they both share Common Features
Can someone please indicate if SQL Compact will support functions like
Average, STDEv etc
Thanks
Touraj
Using SSIS Security Features
Apr 22, 2007I have simple package and trying to use the security feature of ssis. I set the protection level as "EncryptSensitiveWithPassword" and also specify the password. But it doesn't prompts me for the password. Can anybody help me in that?
View 1 Replies View RelatedHow To Use Web Services And Interactive Features
Dec 26, 2006I've been having trouble getting my RS2005 reports with interactive features (drilldowns) to work in an Iframe and rendered with SOAP.
The main problem now is that the toggle buttons (+ / -) point to the reporting services server and do a URL Access call. I've tried the <ReplacementRoot> element in the DeviceInfo parameter but it doesn't appear to do anything.
I keep on reading about how everything is possible through the web services but it looks as if these interactive features are only available using the URL Access. Is this true?
Kwan
SQL Server Express 2005 Features
Sep 2, 2006Hi All, Just curious about the features that are not available in the Express version. Can you import data from other sources? Can you perform DTS packages?
View 1 Replies View RelatedLack Of Enterprise Features Support.
Oct 2, 2006Hi,I've read that SQL Server 2005 ED lacks 'enterprise feature support'. What does this mean?
View 5 Replies View RelatedDynamically Creating Features Grid
Mar 17, 2006I was given what at first seemed a simple task (and maybe it is, but with everything else on my plate, I can't seem to get my head wrapped around this)...create a grid or table something similar to below:
Plan A Plan B PlanC PlanD
Item1 yes yes no no
Item2 yes yes no no
Item3 yes no yes no
etc.....
The "plans" are stored in one db table, the "items" in another. What I can't seem to get straight in my mind is how to relate the yess and nos and then generate a tabular layout similar to the above.
I don't need anyone to do it for me, but a push in the right direction would sure be nice (using SQL 2005) Thanks!
Advanced Topics/features/usage Of DTS's
Mar 13, 2007Hello,
I know the basics of DTSs and have been using them for a while. Now I REALLY want to learn the advanced concepts related to DTS's e.g.
How to make use of the "Inner Package Global Variables" Tab (which is visible in the DTS Designer "window")
How to make use of the "Outer Package Global Variables" Tab (which is visible in the DTS Designer "window")
How to make use of the "Outer Package Global Variables" Tab (which is visible in the menu bar option #4 Designer "window")
Thanks.
Advanced Topics/features/usage Of DTS's
Mar 13, 2007Hello,
I know the basics of DTSs and have been using them for a while. Now I REALLY want to learn the advanced concepts related to DTS's e.g.
How to make use of the "Inner Package Global Variables" Tab (which is visible in the DTS Designer "window")
How to make use of the "Outer Package Global Variables" Tab (which is visible in the DTS Designer "window")
How to make use of the "Outer Package Global Variables" Tab (which is visible in the menu bar option #4 Designer "window")
Many advance thanks for your help.
SQL 2012 :: Add Features With Existing Installation?
Oct 29, 2014We use SQL Server 2012 sp1.We want to install a feature that we did not install the first time.
So we run the "SQL Server Installation Center" from our server.At the step "Setup Support Rules", we received this error:
Rule "consistency validation for sql server registry keys" failed.
The SQL Server registry keys from a prior installation cannot be modified.
To continue, see SQL Server Setup documentation about how to fix registry keys.
Workgroup Edition SSIS Features?
May 1, 2006Hello,
If I have an instance of SQL Server 2005 Workgroup, am I able to run simple ETL packages against it? By simple I mean:
1. Extract data from a comma separated flat file.
2. Load the flat file data to an Access (2002/2003) database.
3. Load the flat file data to a SQL 2005 table.
4. Execution of .dtsx package.
5. Ways to execute a package (i.e. can schedule a package to be run with the SQL Server Agent)?
6. Notification (i.e. SMTP email) on events from package (i.e. on error).
I have been using the Developer Edition, which I know has all/most of the features of Enterprise. How do the SSIS features change between Developer and Workgroup editions?
TIA!!!
Reporting Services Advanced Features
Mar 27, 2007I am looking to find more information regarding advanced filtering/report parameters available in SQL Server 2005 Reporting Services. From what I can see, the user can set values for each report parameter and the query defaults to "where x = (param 1) and y = (param 2)" etc. Is there a way that a user can pick parameters that structure a more complex where clause? For example, say I want to filter by all clients enrolled in all of these 3 service programs and have a dog or clients not enrolled in one of the following 3 care programs or clients who live in one of the following cities? So it would be like this:
where (((sp = 1 and sp = 2 and sp = 3) and animal = dog) or ((cp not in (1, 2, 3)) or city in (a, b, c, d)))
In addition, I am looking for more information regarding programmatic capabilities in Reporting Services. Can anyone point me to a good place for this information? I would like to know whether we could structure part of a query in VB.NET code and then pass this query to Reporting Services to join with the basic report query. Also, I would like to know Reporting Services' capability to interact with stored procedures.
In addition, is Reporting Services more for internal business reporting, or do companies add it onto their existing software package to sell to external customers? Does Reporting Services allow one report to be created, and then users (our customers) on different servers who use different databases to run the report by giving Reporting Services their credentials (database, SQL Server login, etc)?
Basically, I have just been following the tutorials on Microsoft's site, but they are all pretty basic so I haven't gotten a good feel for whether this product is good for advanced reporting needs.
Thanks!