Settings for TortoiseSVN on Windows

Update: Added bin *.dll and *.pdb to the string. I put any external resource DLL’s into a seperate folder outside the projects and purposefully add them to Subversion. Then all the dlls elsewhere are not included in subversion… which in theory would increase the size a lot.

In the settings for TortoiseSVN there is a section called Subversion which has some settings that need to be changed when installing on a windows system. Please see the "Official support for Windows ‘_svn’ directories" section on this page
The global Ignore Pattern I set to the following:

*.projdata obj tmp temp *proj.user *.o *.obj *.ncb *.suo *.pyc bin *.dll *.pdb

Also check the ‘Use "_svn" instead of ".svn" directories’ option.

Advertisements

“Cannot Resolve the collation conflict between” error in Sql Server 2005

I’ve come across this issue a few times. You try and compare two columns on different tables where the collation has not been defined consistently for whatever reason, and you get an error like this

Msg 468, Level 16, State 9, Line 21
Cannot resolve the collation conflict between "Latin1_General_CI_AS"
    and "SQL_Latin1_General_CP1_CI_AI" in the equal to operation.

I’ve got an example below that you can run to cause the error. Notice that the Dogs.Name column is set to a collation of “SQL_Latin1_General_CP1_CI_AI” and the OtherListOfDogs.DogName column is set to a collation of “Latin1_General_CI_AS”.

DECLARE @Dogs TABLE
(
    DogID int
    ,Age int
    ,[Name] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AI
)

DECLARE @OtherListOfDogs TABLE
(
    DogName NVARCHAR(100) COLLATE Latin1_General_CI_AS
)

SELECT  *
FROM    @Dogs d
JOIN    @OtherListOfDogs olod
    ON  d.[Name] = olod.DogName

The two columns are compared in the select statement. The collations are different and they are incompatible, thus the error.

To allow the code to work (notice I said work, not necessarily “fix”) you can add a collate statement to one of the columns (or both) like in the following:

SELECT  *
FROM    @Dogs d
JOIN    @OtherListOfDogs olod
    ON  d.[Name] COLLATE Latin1_General_CI_AS = olod.DogName

So the Dogs.Name column will be compared to the other tables column with the same collation… And no error. :o)

Call a bat file from a bat file

Rather simple thing that I keep forgetting. I keep thinking this is Exec like in Sql.

To call a batch file from within another batch file you put the keyword “CALL” before the call to the batch file:

CALL MyOtherBatchFile.bat

So simply. So easy to forget when you don’t use it very often!

Enabling and Disabling Triggers

Rather simple this:

Disable a Trigger

ALTER TABLE [dbo].[TableWithTrigger] DISABLE TRIGGER [MyTrigger]
GO

Enable a Trigger

ALTER TABLE [dbo].[TableWithTrigger] ENABLE TRIGGER [MyTrigger]
GO

Concatenating Sql Scripts

To concatenate Sql Scripts into one big sql script I usually use the following rules and a batch file:

  • Always put a “GO” at the end of each script. This ensures that one script doesn’t blend with another one in the same batch.
  • Always put at least one blank line at the end and start of the script (I just do this to prevent concatenation of files resulting in the GO and something else joining together and causing an error)
  • Each file needs to be in the same encoding. See here
  • I try and order the scripts by putting a number at the start of each script. The number will help keep them in order in Explorer and also will mean they should concatenate in the correct order

I create a batch file called Concatenate.bat which has the following contents:

DEL @MyBigScript.sql
COPY /A *.sql @MyBigScript.sql

This removes the file called @MyBigScript.sql and then concatenates all .sql files into a new file called @MyBigScript.sql.

Update: The @ at the start of the filename will make the @MyBigScript.sql file show at the top of Explorer.

Login failed for user …

I was just comparing two backups against another database. I had restored one database and compared it with Sql Compare from Redgate. All good! Then I restored another database to compare against the same database. No go….

“Login failed for user bla“.

Error ID: was 18456.

Solution:

Silly me! When i restored the second database, I had given it a name different to the first one!

Lesson:

If anything goes wrong “Check the servername, username, password, AND database name!” or in other words check all the connection string/credentials completely!!!

Saving an Sql Script in a Different Encoding

In some situations I create scripts and store them in a folder. Later I will concatenate the contents using a batch file which doesn’t work very well if the files do not use the same encoding.If you have one in Codepage 1200 and another in Codepage 1252 then you will have some very odd characters in your resulting script

When writing a script in Sql Server Management Studio, you can save them with different encoding. I learnt the idea of a previous employee at work and found it useful.

By default Management Studio seems to want to save new scripts (press “New Query” button) as “Western European (Windows) – Codepage 1252”, yet when you click “Modify” to edit a stored procedure, for example, and then try and save the script it will, by default, save as “Unicode – Codepage 1200″… and these don’t concatenate together very well as already explained.

There seems to be know way within Management Studio to set a default for this, so unless I’m mistaken, we are stuck doing things manually. Here is how:

When you have the Save File dialog, click the little drop down box at the right of the “Save” button, and click “Save with Encoding…” option.

Sql Management Studio - Save with Encoding...

Sql Management Studio - Save with Encoding...

You can now select the type of encoding.

I had done a fair bit of work with Management Studio before I came across this small drop down on the save button. It’s not really obvious as I was expecting a drop down box on the actual dialog. Hopefully this will be helpful to someone else also.