Truncating a table with foreign keys

So  your in development, and you have a need to truncate a table.  You write your all-too-familiar “TRUNCATE TABLE MySchema.MyTable” But wait, you get this error message when you try and execute

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table ‘MySchema.MyTable’ because it is being referenced by a FOREIGN KEY constraint.

What? Why? This error is telling you that your table is being referenced by another table in the database, and cannot be truncated. But what if you have already truncated the other table and you really do want to empty it?

To get around this, run a DELETE FROM command and do not put in a where clause. So your command would be:

DELETE FROM Myschema.MyTable

Now you have an empty table, but your identity column, if you have one, is going to be off. To correct it, run a dbcc command to reset it:

DBCC CHECKIDENT (‘MyTable’, RESEED, 0)

Where MyTable is the name of your table. Please note, if your table is not in the dbo schema, you will have to specify the schema name and then the table name. Like (MySchema.MyTable). And 0 is the number you want to reseed to -1 (meaning that, in this case, the next row inserted would have an identity value of 1)

Hope it helps you in your travels.

Using the netonly switch

The company I currently work for keeps all of their environments in separate domains. Not an uncommon scenario, so OK, we can work with it. However, the challenge has always been how to have a shared database development location that everyone can use without remoting over to the machine. The problem with that is handling multiple people. It works fine until you reach a predetermined number of developers (2). Since the development machines are in a different domain, simply right-clicking, selecting runas, and passing new credentials doesn’t work. The answer, as with most things, lies in a simple change. It’s called netonly, and it’s extremely handy.

To use it, open up notepad, type in the code, and then save it as a batch file.  Then you can authenticate across! I use this regularly to connect across the domain to our servers in our development domain while staying on my local machine. DOMAIN\username is the domain\username that you want to pass in.

runas /netonly /user:DOMAIN\username  “Program File Path”

For example, for SQL Server Management Studio 2008 R2 the file path would be below (on 32 bit windows xp, don’t ask :)):

runas /netonly /user:DOMAIN\username “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ssms.exe”

NOW we’re getting somewhere. Next point is what if you want to pass the DOMAIN\username in as a variable(so you can pass this awesome new tool you found around to everyone in your shop)?

Again, the answer is just a quick change to our code.

@echo off

set /p user=”Username: ”

runas /netonly /user:%user% “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe”

Now we can take this and use to authenticate quickly and easily across domains using the credentials we want, even when crossing to a different domain.

 

Hope it helps you in your travels.

 

One more quick note: This is my first blog post, so feedback is appreciated. 🙂