Hi folks, I have (what I think) is a simple Sql Server spatial query: Grab all the USA States that exist inside some 4 sided polygon (ie. the viewport/bounding box of a web page's google/bing map) SELECT CAST(2 AS TINYINT) AS LocationType, a.Name AS FullName, StateId, a.Name, Boundary.STAsText() AS Boundary, CentrePoint.STAsT...
I would like to know what the implications for my database might be to swap from compat mode 90 (2005) to 100 (2008). Are there any performance enhancements? Are there any deprecated features? ...
I have a table: declare @Table table (XmlPart xml, Cnt int) the XmlPart is of the following form: <Group count="0" /> I would like to modify XmlPart by substituting it with value of Cnt column. That's what I try: update @Table set XmlPart.modify('replace value of (/Group/@count)[1] with sql:column(Cnt)') But the parser doesn't u...
Can anybody tell why is this query not working? DECLARE @unwantedRows TABLE ( ProductId INT, ProductName VARCHAR(50), Description VARCHAR(50), Category VARCHAR(50), Repetitions VARCHAR(50) ); Select * INTO @unwantedRows From ( Select a.*,Row_Number() Over(Partition By ProductId Order By ProductId) As [Repetiton...
Hi, I have several *.sql files with script to create table and sprocs for a new database. Is there a way to create a TSQL script that could be run from the SQL Query Analyzer that would sequentially call the *.sql files? Example: call scrip 01; call scrip 02; call scrip 03; GO Sorry but I was not able to express my problem properly b...
Hello, Why is this query not working? Delete tblProduct From tblProduct Inner Join @unwantedRows On tblProduct.ProductId = @unwantedRows.ProductId; Where @unwantedRows is DECLARE @unwantedRows TABLE ( ProductId INT, ProductName VARCHAR(50), Description VARCHAR(50), Category VARCHAR(50), Repetitions int ); ...
Hello, I want this procedure to be executed everyday 2:00 a.m. Also, i want to pass date parameter to the procedure. Suppose today is 28th july, 2010, then i want to pass 27th july, 2:00 am to the procedure. This way i want that all records inserted after 27th july, 2:00 am should be backed up in some other table. This is my procedure. ...
Hi, I have a database in sql server 2008. I want to convert it in it's xml format. What I have to do? What is the procedure ? Please guide me. I don't want to write the code for this in any language. Is there any facility given in sql server 2008 ? ...
I'm in the midst of writing a script that is updating the collation of all varchar columns for every table in my database (Sql Server 2008). I need a way to collect a list of each constraint in a table so that I can drop it, alter the columns the constraint affects, and then re-add the constraint. I know that I am able to grab UK constra...
These two queries seem to return the same results. Is that coincidental or are they really the same? 1. SELECT t.ItemNumber, (SELECT TOP 1 ItemDescription FROM Transactions WHERE ItemNumber = t.ItemNumber ORDER BY DateCreated DESC) AS ItemDescription FROM Transactions t GROUP BY t.ItemNumber 2. SELECT DISTINCT(t.ItemNumb...
I'm analyzing some code that utilizes empty OVER clauses in the contest of Count(). Example: SELECT ROW_NUMBER() OVER (ORDER BY Priority DESC) AS RowID, CAST((COUNT(*) OVER() / @pagesize) AS Int) AS TotalPages, I'm trying to understand why the empty OVER clause is being used here. There are other standard select ele...
There are two ways to create a constraint, Method A: ALTER TABLE dbo.<tablename> ADD CONSTRAINT <namingconventionconstraint> UNIQUE NONCLUSTERED ( <columnname> Method B: CREATE UNIQUE NONCLUSTERED INDEX <namingconventionconstraint> ON dbo.<tablename> ( <columnname> ) ON [PRIMARY] However, it appears that these constraints need to ...
I know there are ways to automate SQL Server "unit tests." But my question is slightly different. When validating requirements we write a series of SQL Scripts that basically return nothing if success. So basically its like Execute Query Execute another Query Run SSIS Package Run Query. And example with a little more context ...
Description: I have a column(EmailsAdress) on a table(BusinessUsers) on my databases that stores email address. Problem: Some of the rows of data have a dot at the beginning of this column for example [email protected] (The dot i want to get rid of is the dot just before the charater j in jane) Some of the rows of data have a...
Hi everyone, So, I'm working on a database that I will be adding to my future projects as sort of a supporting db, but I'm having a bit of an issue with it, especially the logs. The database basically needs to be updated once a month. The main table has to be purged and then refilled off of a CSV file. The problem is that Sql Server wi...
I have a ton Visual FoxPro db files that I am trying to import into an empty SQL 2008 Express database. When I run through the SQL Import and Export Wizard everything seems to communicate fine. When I get to the mappings section I can click on preview and see the data in the selected FP table. When I click on Edit Mappings or Next I g...
Hi every one Nice job! Using of Sql Express Management Studio 2008 GUI(not with coding),how can I make a primary key auto-incremented? Let me explain,there is a table which has a column named "id" and the items of this column are set to be primary keys. I want to make this column auto-incremented,but how? Cheers ...
Hi folks, Is it possible to create an indexed view which returns the following results :- ID | Location Name | Aliases for that Location 1 | Some Location | Alias 1, Alias 2, Alias 3 2 | Another Location | NULL 3 | Yet Another Location | NULL 4 | Last location | An Alias My table structure is Location Table LocationId INTEGER Nam...
I have a table with records which include a datetime column "CreationDate". I need to get the following information for every of the last 90 days: How many records were there in total in existence How many records were added on that day I could do this through a loop of counting of course, but this would hit the database 90 times.....
Let's say I have a table like this in my DB Date ItemId Count 7/29/2010 3 1 7/30/2010 3 2 7/31/2010 2 3 7/29/2010 3 4 7/30/2010 1 5 7/31/2010 1 6 7/29/2010 2 7 7/30/2010 3 8 7/31/2010 1 2 For each item type, I want to select the count on the latest date found in the table only. Is there an ea...