Select Count(Distinct Value) returns 1

I’m designing a query in SSMS 2005 which looks something like this: SELECT COUNT(DISTINCT ColumnName) FROM Table WHERE ColumnName IS NOT NULL When I run the query with COUNT() it returns the value 1. When I run it without COUNT(), SSMS reports the correct value eg 212 records. The column in question is of datatype […]

Exit and rollback everything in script on error

I have a TSQL script that does a lot of database structure adjustments but it’s not really safe to just let it go through when something fails. to make things clear: using MS SQL 2005 it’s NOT a stored procedure, just a script file (.sql) what I have is something in the following order BEGIN […]

How to “Open” XML data in Oracle

Here is an example of some TSQL that I would like to rewrite in PL/SQL. DECLARE @xml XML SET @xml = ‘<theRange> <theRow><First>Bob</First><Last>Smith</Last><Age>30</Age></theRow> <theRow><First>Sue</First><Last>Jones</Last><Age>34</Age></theRow> <theRow><First>John</First><Last>Bates</Last><Age>40</Age></theRow> </theRange>’ ;WITH OpenedXML AS ( SELECT r.value(‘First[1]’,’varchar(50)’) AS First, r.value(‘Last[1]’,’varchar(50)’) AS Last, r.value(‘Age[1]’,’int’) AS Age FROM @xml.nodes(‘//theRange/theRow’) AS Row(r) ) SELECT * FROM OpenedXML WHERE Age BETWEEN 30 AND 35 […]

Handling very large strings between SQL Server and .NET code +LINQ

I have an app that needs to handle very large strings between a SQL Server database and .NET code. I have a LINQ query that generates the strings when saving them to the database, but when trying to create the strings from the database, the app crashes with an OutOfMemoryException because of the size of […]

Hiberate problems, jdbc IDENTITY_INSERT is set to OFF

I am getting JDBC error when I attempt a commit through hibernate to SQL Server Cannot insert explicit value for identity column in table ‘Report’ when IDENTITY_INSERT is set to OFF I am using mappings generated by netbeans that contain, <class name=”orm.generated.Report” table=”Report” schema=”dbo” catalog=”DatabaseName”> <id name=”id” type=”int”> <column name=”ID” /> <generator class=”assigned” /> </id> […]

DB design help – EAV + Form builder

I’m trying to build a sort of form builder that’ll allow me to define, display and store ‘tests’ in a flexible way. I.e. Allow the user, through the web interface, to create a new type of test/form (“Grouping”) and define a set of fields that will be displayed on the form (any type of field, […]

How do I add months to a CURRENT_TIMESTAMP in SQL?

How can I add months to the CURRENT_TIMESTAMP in SQL Server? The solution probably lies in DATEADD() but this works with a date only, not a datetime. Thanks.

Replace “&lt;” and “&gt;” with “<” and “>” in sql server

Hi I am new to for xml I have a query like this SELECT ProjectId, ProjectCode, ProjectName, TechId, — LocationId, ( SELECT GeoId,PoliticalDivisionId ,GeographicLocationName,IsoCode,Longitude,Latitude,ParentLocationId, t2.CreatedBy,t2.CreatedOn,t2.LastUpdatedBy,t2.LastUpdatedOn FROM GeographicLocation t2 WHERE GeoId = t1.LocationId FOR XML PATH(‘Location’) ), RtoId, CreatedBy, CreatedOn, LastUpdatedBy, LastUpdatedOn FROM Project t1 where ProjectId=1 FOR XML PATH(‘ProjectInfo’) it return the xml as <ProjectInfo> […]

Comparing stored procedure performance ex and new version

I did create two new indexes on the tables that are used on a the sp. The new results shows that on the part of problematic joins, the scans are converted to seek. I think seek is better rather than scan operations. On the other hand, the time takes more or less the same duration […]

SQL Server version of Oracle's CONNECT BY in LINQ to show hierachy

I have successfully simulated an Oracle CONNECT BY statement in SQL Server 2008 by following these 2 previous answers here and here and adjusting to get the results I need. But how do I do this in LINQ? Here is an example of what I am doing using a dummy database: CREATE TABLE Employee( EmployeeID […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.