IGNORE_DUP_KEY = ON basically tells SQL Server to insert non-duplicate rows, but silently ignore any duplicates; the default behavior is to raise an error and abort the entire transaction when there are duplicates in a column that doesn't allow them. I've worked with a ton of data that normally has at least one duplicate when there shou...
Is there an Implode type function for SQL Server? What I have is a list (in a SQL server table): Apple Orange Pear Blueberry and I want them to come out as Apple, Orange, Pear, Blueberry I hope for the space and comma to be configurable but I can always replace it if it isn't... Quick help would be appreciated! ...
Hello All, I am having some issues throwing together a LINQ query that will join a table based on a zip code. I need to join the table based on whether the customer's zip code lies with in a range of zip codes that is determined by ZIPBEG and ZIPEND columns. The T-SQL would look something like this: JOIN [ZipCodeTable] [zips] O...
When restoring my database i have a problem with the physical file of the full text catalog being in use. The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\MyCatalog' cannot be overwritten. It is being used by database 'demo2'. I use this restore statement RESTORE database demo from disk = N'c:\temp\demo.bak' WIT...
Hi, Is there any way in which i can clean a db in SQl Server 2005 i.e by dropping all the tables and deleting stored procedures, triggers, constraints and all the dependencies in one sql statement. REASON FOR REQUEST: I want to have a db script for cleaning up an existing db which is not in use rather than creating new ones, specially ...
Here’s the scenario: You load a page, which renders based on the data in the database (call these the “assumptions”) Someone changes the assumptions You submit your page ERROR! The general pattern to solve this problem is this (right?): In your save proc, inside a begin and commit transaction, you validate your assumptions first. ...
Consider the following table: create table temp ( name int, a int, b int ) insert into temp (name, a, b) values (1, 2, 3) insert into temp (name, a, b) values (1, 4, 5) insert into temp (name, a, b) values (2, 6, 7) I want to select *(all fields) with distinct [name]. In the case of two or more rows having the ...
Duplicate of http://stackoverflow.com/questions/2775/whats-the-best-way-to-remove-the-time-portion-of-a-datetime-value-sql-server I have a column that tracks when things are created using a datetime, but I'd like to generate a report that groups then by day, so I need a way of nulling out the time component of a datetime column. How do...
I have a "copy" stored procedure for a database. During the copy, I backup the database with something like: exec('backup database [' + @source + '] ' + 'to disk = N''' + @backupdir + '\copybackup'' ' + 'with noformat, noinit, name=N''copybackup-full'', ' + 'SKIP, NOREWIND, NOUNLOAD, STATS = 10;'); And then create and emp...
I have two tables, one an import table, the other a FK constraint on the table the import table will eventually be put into. In the import table a user can provide a list of semicolon separated values that correspond to values in the 2nd table. So we're looking at something like this: TABLE 1 ID | Column1 1 | A; B; C; D TABLE 2 ID ...
I am using SQL Server 2005 and I have a XML Data Type column that stores xml fragments like so in one big table: row 1 ..... <Order date='2009-02-11' customerID='4' /> row 2...... <OrderItem OrderID='6' ItemID='477' quantity='1' /> I would like to create an XML using T-SQL that looks like this from these nodes: <Orders> <Order data...
You might be aware of the Halloween problem in which a an SQL cursor can be a nightmare for you if not properly used. What are the other subtle problems (difficult to debug and detect) that might be introduced if one does not carefully make use of SQL 2005 features? ...
Assume I've a sql statement like the following with the variable @FOO set somewhere earlier in code: SELECT FIELDLIST FROM TABLE WHERE (FIELD = @FOO OR @FOO IS NULL) Is the query optimizer smart enough to do the second side of the OR first (@FOO IS NULL) because (another assumption) it is faster to do a null check than it is to d...
The following (sanitized) code sometimes produces these errors: Cannot drop the table 'database.dbo.Table', because it does not exist or you do not have permission. There is already an object named 'Table' in the database. begin transaction if exists (select 1 from database.Sys.Tables where name ='Table') begin d...
I need to shorten this query and while I'm pretty good at SQL, I'm still learning. SELECT 'doejoh', DATETIME, [Recipient-Address], [Message-Subject], [Sender-Address] FROM dbo.Logs WHERE LEFT([Recipient-Address], 6) IN ('doejoh') UNION ALL SELECT 'doejoh', DATET...
I'm trying to learn advanced sql and how to use system queries (sql server). The below query is a little confusing. CREATE PROC dbo.ShowHierarchy ( @Root int ) AS BEGIN SET NOCOUNT ON DECLARE @EmpID int, @EmpName varchar(30) SET @EmpName = (SELECT EmpName FROM dbo.Emp WHERE EmpID = @Root) PRINT REPLICATE('-', @@NEST...
For example, for heavily used tables with volumes in the order of 10 million rows that grow by a million rows a month, if the stats are 6-8 months old how detrimental to the performance of the database is this going to be? How often should you be refreshing the stats? ...
Hi All, I've inherited the code below but I'm puzzled as the code works just fine but I'm not convinced it's correct. I've not used LIKE before so I can't decide if I should bother changing this. How does this look to you? The code is for a filter in a db it checks if X value contains Y value, find X* in X (e.g. Match would be F* in Fu...
Is there command inside a Query Window that will open a stored procedure in another Query Window? i.e. MODIFY dbo.pCreateGarnishmentForEmployee I am using SQL Server management Studio 2005 and Red Gate's SQL Prompt. Currently I have to do the follwowing multiple steps: Open Object Explorer Navigate Programmability | Stored Procedure ...
How can you search all the Stored Procedures for a Pattern and then open the stored procedures to be edited? Is there anything built inside of SQL Server 2005? Or are there any 3rd party addins that will do this searching? I am also using Red Gate's SQL Prompt but I have not noticed that option. Currently I am using the following co...