I'll be using the AdventureWorks Database to illustrate my problem. I need to show for a particular customer a list of OrderDate with the most Orders. My intial attempt was as follows: SELECT CustomerID, OrderDate, COUNT(1) Cnt FROM Sales.SalesOrderHeader WHERE CustomerID = 11300 GROUP BY CustomerID, OrderDate ORDER BY Cnt DESC This...
Hi, i am new in SqlServer.When i insert 07.12.2008 12:34:00 into my smalldatetime type column,it is inserted but when i select it in query analyzer,i see it as 2008-12-07 12:34:00.How can i change default format of that column? ...
I have my type "x" of type varchar(50). How can I alter it to varchar(100)? It seems I can't! ...
Hey, I'm having an issue that appears to be related to collation, but I'm not sure. I'm developing against a backup of my production database. The only difference in the SQL Server 2005 environments (that I'm aware of right now) is that my localhost has "SQL_Latin1_General_CP1_CS_AS" (Case sensitive) as the collation, and the production...
I've created a model using SQL Server Reporting Services. When I try to create a report using the Report Builder, I can drag an entity to the report, followed by a nested sub-entity. I get the two-level grouping as expected. However, I cannot add a third level entity to the relationship, no matter which combination I try. What can I do...
I have been researching audit triggers for some of my sql tables. I came across this article on simple-talk. Now I know that the COLUMNS_UPDATED() function used here is only supposed to be available on SQL Server 2005 and later but it appears to work under 2000 as well. Is this perhaps a classic Microsoft undocumented feature in 2000? O...
I've been doing a convert(varchar,datefield,112) on each date field that I'm using in 'between' queries in SQL server to ensure that I'm only accounting for dates and not missing any based on the time part of datetime fields. Now, I'm hearing that the converts aren't indexable and that there are better methods, in SQL Server 2005, to co...
I would need to create a temp table for paging purposes. I would be selecting all records into a temp table and then do further processing with it. I am wondering which of the following is a better approach: 1) Select all the columns of my Primary Table into the Temp Table and then being able to select the rows I would need OR 2) Sel...
I have the following code in a SQL Server 2005 trigger: CREATE TRIGGER [myTrigger] ON [myTable] FOR UPDATE,DELETE AS BEGIN DECLARE @OperationType VARCHAR(6) IF EXISTS(SELECT 1 FROM INSERTED) BEGIN SET @OperationType='Update' END ELSE BEGIN SET @OperationType='Delete' END My question: is there a situation in which @Operation...
DBCC SHRINKFILE always works when I run it manually on a log file, even when I get the following message: 'Cannot shrink log file 2 (Claim_Log) because all logical log files are in use.' When I run it from a job, however, it only shrinks the log about one third of the time. The other times, it just remains large (about 150Gb). There i...
On a SQL Server 2005 database, one of our remote developers just checked in a change to a stored procedure that changed a "select scope_identity" to "select @@identity". Do you know of any reasons why you'd use @@identity over scope_identity? ...
I am new to SQL Server, and I have been tasked with setting permissions on a remote server. What I need to do is limit the domain admin from having access to any of the DB's on a particular server. How can I accomplish this? ...
I'm working with SQL Server 2005 and Windows Server 2000 and wonder if there are any 'automated' ways of blocking SQL Injection attacks while I shore up my code. Some have suggested that there are ways to: Put in some kind of ISAPI or HTTP module that filters request post and querystrings for injection-oriented symbols and fails the r...
I'm porting a process which creates a MASSIVE CROSS JOIN of two tables. The resulting table contains 15m records (looks like the process makes a 30m cross join with a 2600 row table and a 12000 row table and then does some grouping which must split it in half). The rows are relatively narrow - just 6 columns. It's been running for 5 h...
I have following situation. A main table and many other tables linked together with foreign keys. Now when I would like to delete a row in the main table a ConstraintsViolation will occur, which is intended and good. Now I want to be able to check if the ConstraintsViolation will occur before I trigger the delete row event. Is this po...
Hi Guys, Is it possible to connect to my remote database hosted at godaddy from locally from my PC? And if yes how? I tried the connection string provided to me but it didn't work. Thanks ...
Because I am a newbie I am trying to log out any errors that may occur with stored procedures I write. I understand Try/Catch in SQL 2005 and error_procedure(), ERROR_MESSAGE() and the other built in functions. What I can figure out how to do is capture what record caused the error on an update. I could probably use a cursor and loop ...
I just bought Visual Studio 2008 Professional and it came with SQL Server 2005 Developer Edition. I'm used to using SQL Server 2005 at work, but the Developer edition doesn't seem to come with Server Management Studio, so I'm at a bit of a loss. A few questions: What resources are there for configuring and setting up SQL Server 2005 ...
Hi, Can anybody let me know the query to find all the tables that has a date columns on it. Thanks ...
How to find fifth highest salary in a single query in MS Sql Server ...