… may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Chances are, you are here because you have googled for the above mentioned error from SQL Server.

In case you did not understand what it means, I’ve found a simple yet effective explanation in this link

I quote

CREATE TABLE foo (
fooid int IDENTITY,
foovalue varchar(47)
)

CREATE TABLE bar (
barid int IDENTITY,
barvalue varchar(47),
fooid int
CONSTRAINT fk_bar_foo FOREIGN KEY(fooid)
REFERENCES foo(fooid)
ON UPDATE
CASCADE
ON DELETE
CASCADE
)

CREATE TABLE baz (
bazid int IDENTITY,
bazvalue varchar(47),
barid int
CONSTRAINT fk_baz_bar FOREIGN KEY(barid)
REFERENCES bar(barid)
ON UPDATE
CASCADE
ON DELETE
CASCADE
)

SQL Server will say, hey, if we delete a row from foo, well, we could delete
multiple rows from bar, and then we’ll have to delete multiple rows from
baz, and that could go on forever! So let’s issue a warning about “cycles or
multiple cascade paths” and disallow it.

SQL Server 2000 vs SQL Server 2005 queries

This gives an example of SQL queries between SQL Server 2000  and SQL Server 2005

http://www.mssqltips.com/tip.asp?tip=1015

Bumped into this while looking for  IGNORE_DUP_KEY

 

Also realized you can’t put this as part of a table creation unlike SQL Server 2005.

SELECT INTO #temporary-tables bottlenecks in SQL Server 6.5 and 7

Early this week, I stumbled into a puzzling error.

I created a temporary table in a store procedure, exec an sql statement, and select * records in that temporary table. Code pasted below

CREATE TABLE #TmpCareModel
(
EpisodeID INT ,
EventDate VARCHAR(12),
ID INT,
EventCareModel INT
)

SELECT @SQL = ‘SELECT EpisodeID, MAX(Convert(Datetime, EventDate,103)) MaxDate, ID, EventCareModel INTO #TmpCareModel
FROM ObAnteEvent WHERE EventType = ”CareModel” AND EventCareModel = EventCareModel AND EventDate <> ”” GROUP BY EpisodeID, ID, EventCareModel ‘

EXEC (@SQL)
SELECT * FROM #TmpCareModel

Simple straight-forward case. However, during execution there are no records returned (although it is supposed to) and when I execute it in Query manager (cut and paste the same sql) there are records returned.

I suspected it was something to do with ‘SELECT INTO #temporary-table’ and googled for an answer. Neil Boyle wrote an article about avoiding temporary table bottlenecks and mentioned the implications of writing a direct SELECT INTO #temporary-table in SQL Server version 6.5 and 7

When I tried his solution, it worked! Well, here’s the quote from his article

create table #temp( ........ ) insert #temp select * from sourceTable 

 

Set Up Backup Schedules (Maintenance Plan Wizard)

backing up your SQL 2000 database? fred not, configure it to automatically do it via link here

there’s other nice features there as well.

Date formats from SQL Server to Ms Excel.

Have you ever generated an Excel report only to find that the dates have been formatted incorrectly? But not all of them are in incorrect order, only some dates are in incorrect format. Dates are normally converted to American time format (mm/dd/yyyy), but when it is unable to do so (month > 12 = error) then it leaves it as dd/mm/yyyy

Well, we manage to solve the error by reformatting the date cells once again, but we didn’t understand why Excel did that, and now the performance of the system is very slow because of reformatting cells.

Martin Green wrote an article about the same issue on dates and provides an unofficial understanding of what Excel is doing behind those codes. Apparently, Excel handles dates differently when you enter them directly on worksheets and when the dates come through the VBA codes. According to him, the best bet is to deliver the dates in ISO format, because Excel and VBA will correctly convert them as dd/mm/yyyy on the cells.

This article shows the different styles for date format in SQL Server. It is useful when your date in SQL Server is “smalldatetime”, but the dates in my server is currently all in varchar format (don’t ask me why…)

So in order to convert the current format dd/mm/yyyy to ISO format yyyy/mm/dd, I wrote this

CAST(DATEPART([Year], CONVERT(DateTime, BirthDate, 103)) AS varchar) + ”/” + CAST(DATEPART([Month], CONVERT(DateTime, BirthDate, 103)) AS varchar) + ”/” + CAST(DATEPART([Day], CONVERT(DateTime, BirthDate, 103)) AS varchar) AS BirthDate

(Style 103 is dd/mm/yyyy)  Jeff Houser gives a better explanation on casting dates

 

Backup SQL Server 2000 using SQL Agent

Yesterday I learnt my lesson. I made a local copy of database on my server and I forgot to migrate it back to the company server, and the data in the tables were already “dirty” after being manipulated, so I just deleted it. One hour later, I realized my store procedures were not migrated at all. And tried recovering it, but to no avail. So, I had to write my store procedures again. Good thing is, its 3 small ones. And the lesson is: BAcKUP! whatever you do!

But I am really lazy to manually back things up. Seriously, as a programmer, I remember what codes I’ve written and the logic of programs, but plainly remembering to back things up…that is so easily forgotten. And so, I was determined to set a backup schedule for all my databases in my local server, just in case I accidentally wipe out another database again.

SQL Server Backup and Recovery Learning Guide gives a quick guide as to how you are going to do backups, but its not exactly what I was looking for. Nevertheless, useful to keep in the bookmark.

This is Chapter 14 Working with SQL Server Agent Jobs, taken from Microsoft SQL Server 2005 Implementation and Maintenance MCTS Exam 70-431 Self Training Kit. More catered for SQL Server 2005, but still similar to SQL Server 2000. Might require some free sign up in order to read the contents.