How to execute a huge SQL file with Microsoft Sql Server

Here is a small article that could be more a note than something else. If you have a lot of SQL statement to execute, let say few gigs of statement, this won’t load into SQL Server Manager. What you can do is to use the sqlcmd command. Open a DOS console and go where you have installed Sql Server Mananger. Mine is:

c:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn

In the console, you need to specify the server and instance you want to execute the file to. Optionnaly, you can write the output to a file.

sqlcmd -S myServer\instanceName -i C:\myScript.sql -o C:\log.txt

That’s it. Nothing more complicated.

How to modify a FK constraint with Microsoft Server Management Studio 2008 R2

This task should be easy isn’t?

Well, if you go to the Microsoft Server Management Studio (2008 R2) designer to do it and that the constraint is located into a schema other than DBO, this might be more complex that you think.

Rename failed for ForeignKey ‘FK__ABC’. Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong. (Microsoft SQL Server, Error: 15248)

What’s happening it that the tool is trying to use SP_RENAME with DBO as the schema. This can be verified with the Sql Profiler. It’s also a known bug that Microsoft should have fixed in their latest release of SQL Server.

To be able to change the foreign key constrain name, you need to do it manually with the SP_RENAME function.

The syntax is ‘YourSchema.YourFKNameToChange’ followed by only the FK you want. Here is an example:

sp_rename 'YourSchema.YourFKNameToChange', 'YourNewFKName', 'OBJECT'

That’s it!