SQL: Do Accent Insentitive (AI) and Case Insentitive (CI) join

SQL Tip:

--do Accent Insentitive (AI) and Case Insentitive (CI) join
select *
from [TableA] a
left outer join [TableB] b
	on a.[AID] COLLATE Latin1_general_CI_AI = b.[AID] COLLATE Latin1_general_CI_AI

A couple of links:

• http://blog.sqlauthority.com/2007/06/11/sql-server-cannot-resolve-collation-conflict-for-equal-to-operation/
• http://stackoverflow.com/questions/2461522/how-do-i-perform-an-accent-insensitive-compare-e-with-e-e-e-and-e-in-sql-serv

SQL: Replace CRLF

So how to remove CRLF from a select mycolumn from mytable?

Here is a Catch-All solution:

REPLACE(REPLACE(REPLACE(REPLACE(MyTable.MyColumn, CHAR(13) + CHAR(10), ' '), CHAR(10) + CHAR(13), ' '), CHAR(13), ' '), CHAR(10), ' ')

Thanx to Rex Tang.

But I just want to remove CRLF:

REPLACE(MyTable.MyColumn, CHAR(13) + CHAR(10), '')

Microsoft offerings – and BizSpark content

Was looking for a Microsoft offer that could give OS’s and Servers to play with at home. Found a few interesting programs: 

• BizSpark. Comments from Netmedia
• WebsiteSpark
• Windows Azure Platform
• Partner (Action Pack)

SQL2005: Create a unique key with Row_number()

A datawash solution:

Problem: From Active Directory I had a long list, where column alias was not unique.
Instead of do any manual filtering away the aliases that were found more than once, I just wanted SQL server to throw away the duplets, leaving only one alias bin the list.

SQL2008: Read committed snapshot

I was just told not to use

READ UNCOMMITTED also called dirty read in SQL 2008. A new possibilty exists:

READ COMMITTED SNAPSHOT

The problem with the first one is that if conn A reads a large bit, conn B do update, conn A reaches the updated bit and reads that, conn B rolls back, then conn A got illegal data.

If using the snapshot read, then conn A reads only on a snapshot as long as conn B is doing update. This snapshot contains the before transaction data.

This makes sence because had connection B just been a little later, then it would have been the same situation as with the snapshot option.

Thanks to Jesper Johansen

OLEDB: Create connection string

A little trick to create a OLEDB connection string:

• On a Windows desktop (or any other folder) create a new text file.
• Rename the file to MyNewConnection.udl
• Double click the file which will open the guide Data Link Properties.
• Insert your connection data, test the connection and close the guide by pressing OK
• Open the file in your favorite text editor and view the connectionstring

Examples of generated connection strings:

•  Provider=SQLOLEDB.1;Password=MyPsw;Persist Security Info=True;User ID=MyUser;Initial Catalog=MyDb;Data Source=mydbserver\mydbinstance
• Provider=MSDASQL.1;Persist Security Info=False;MyUser ID=user1;Data Source=MyOdbcDsn;Initial Catalog=MyDB

Thanx to Rakesh Moturi

The End

SQL: Install AdventureWorks

This post shows how to use the SSMS GUI to attach a DB.

1) Download Adventurworks from http://www.codeplex.com/Wiki/View.aspx?ProjectName=SqlServerSamples

2) Install the DB. This will only unpack the mdf file.

3) Attach the DB. Then you can see it in SSMS:

You can either use commands like here: http://www.codeplex.com/SqlServerSamples/Wiki/View.aspx?title=SQLServerDatabasesandSamplesOverview&referringTitle=Home

Or you can browse for the mdf file:

SQL: Object Created date

Where to find Object Created date?

In SSMS have open Object Explorer and Object Explorer details.

The details view of the latter will show created date.

Thanx to Kim Bach

You can also use a query:

USE AdventureWorks;
GO
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND name = 'uspUpdateEmployeeHireInfo'
GO

Thanx to Pinal Dave

The end

BarCampCph Nov 2008 Presentation

Wants to visit BarCamp Copenhagen. The idea is to share knowledge. This forces me to find some topics to precent…

Blogging gives me content for a presentation – A few .NET samples

So what is a topic I can present to catch other peoples interest? Luckily I have started blogging. WordPress (wp) gives me the answer. Wp has a statistics page. You will not be able to follow that link, since you need admin rights. 

SQL: HowTo import new rows to a Domain table from a flat file

Terminology:

• Domain table: A table with a function like an Enum in C#. In some cases it needs new lines, but never needs changes.
• ImportTableTemp: a table that is a mirror of a flat file with only varchar as column types

PreRequisites:

• Delete all rows from ImportTableTemp
• Import rows to ImportTableTemp from a flat file

Now use the following join to add new rows from the flat file to the domain table.

Note: In this example the Domain table uses a varchar as ID.

Insert Into TargetDomainTable (ID, Name) -- targettable
Select Distinct IT.TTID, IT.TTName -- data from importtable
	From ImportTableTemp IT  -- importtable
	Left Outer Join TargetDomainTable TT On IT.TTID = TT.ID -- all rows from importtable
	Where TT.ID is NULL -- targettable has not data
	AND IT.TTID is not NULL -- importtable has data
	AND IT.TTID != '' -- importtable has data (optional - when TTID is a varchar)

SQL server Backup

I never thought SQL server backup was easy to remember.

This is probably because the restore command involves 3 physical files, 2 logical names and the Db name.

SQL server login

I guess I am not the only one, who thinks the SQL user account setup is a strange mess.

LINQ with VB.NET: Join SQL with a CSV file or List of objects

One of the big advantages with LINQ is that you can do operations on sets in the data access layer instead of doing them in the database.

An example of this is to join a table from a CSV-file with a table in a database. In this example the CSV-file has been loaded into a List(Of CsvRecord), where CsvRecord is an entity object representing the content of the file. Code for that class contains properties for each column. The code is not shown here. For this example it just contains the property CsvId, which is used for joining with the SQL server.

Update of the resultset back to SQL server is possible and easy (Wow), but not shown in this post.

Update or insert with ID

Simple update/insert sample – just nice to have

-- Update MyTable, when ID is known
if (select count(*) from MyTable where MyId = @MyId) > 0
 UPDATE MyTable SET MyColumn = @MyColumn
 WHERE MyId = @MyId
else
 --exec add2MyTable @MyId, @MyColumn
 INSERT INTO MyTable (MyId,MyColumn) VALUES (@MyId,@MyColumn)

ReIndex all

Under master you will find a sp for reindexing. It has different parms, so go there and check them first.

exec sp_sk_reorgDB myDb, 0 , 'Y'

HowTo: Import data to SQL from password protected MDB

SQL server Mgt Studio:

• Select DB – Right click DB – Tasks… – Import data…

=> SQL server Import Wizard opens

• Choose a datasource – Write name – Press Advanced…

=> Datalink Properties opens.

• Tab: “Connection”: Username: “” (Blank)
• Tab: “Connection”: Check “Blank Password”
• Tab: “All”: Jet OLEDB:Database Password: ******* (Enter password)
• Tab: “Connection”: Press “Test Connection”

HowTo: Link to a table in SQL server

From Access:

• Right-click “Tables” – “Link Tables…” – Scroll to file type “ODBC …” – Select desired DSN or create a new one – Select the desired tables – for each table select a column to be unique key
• Rename the linked tables: Remove the prefix dbo_

HowTo: Export tables from Access to SQL Srv

• In Access right-click the source table – “Export…” – “SaveAs” – choose “ODBC” in the bottom of the list – choose default table name – “OK”
• If DSN doesn’t exist create a new “Machine Data Source” (System) – choose driver “SQL server” in the bottom of the list – “Next” – “Finish” – Choose a name for the connection and choose a SQL server – “Next” – “Next” – Change default DB to the target one – “Next” – “Test Connecttion”
• “OK”

HowTo: Change linked mdb-table to another location

In Access:

• Tools -DB Utils – Linked table mgr:
  - Select mdb to relink
  - check “Allways…”
  - Browse to the new mdb file

HowTo: Open .mdb object form

On a password secured app: 

HowTo avoid to open a .mdb file with a limited startup menu and with the object form available:
- Hold down shift:

• when opening the .mdb file
• when pressing OK after entering psw

Unique if not null

From http://www.webservertalk.com/archive132-2004-3-145797.html :

If you are running SQL 2000, another technique is to create an indexed view
to enforce your unique-if-not-null constraint. For example:

System stored procedures and functions

---

SP_COLUMNS view_employe

– output
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH SCALE RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE SS_DATA_TYPE
npr dbo VIEW_employe employeid 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 1 NO 56

npr dbo VIEW_employe employestate 5 smallint 5 2 0 10 1 NULL NULL 5 NULL NULL 2 YES 38

---

<span style="font-size: x-small; color: #0000ff;">SELECT</span><span style="font-size: x-small;"> </span><span style="font-size: x-small; color: #ff00ff;">@@SERVERNAME</span>

-- output
MyServer\AnSQLInstance

---

Print dublets

select firstname + ' ' + lastname as [name], count(*) as counter
from employe
group by firstname + ' ' + lastname
having count(*) > 1
order by counter desc, [name]

-- output
name                    counter
----------------------- -----------
Hallo World             82
Jens Jensen             21

If a search string can be null

http://www.thescripts.com/forum/thread81525.html

-- If a search string can be null
if @shortaddress is null
	select @addid = addid from address
	where shortaddress is null
else
	select @addid = addid from address
	where shortaddress = @shortaddress 

-- can be replaced with
select @addid = addid from address
where isnull(shortaddress,'@£$' ) = isnull(@shortaddress,'@£$' )
-- (where '@£$' is a value, which is not possible to get in that column)

Find the not included

-- Find those addid in the employe table, which is not in the address table:
select addid from
	(select distinct addid from employe) e
where addid not in (
	select a.addid from address a
	inner join
	(select distinct addid from employe) e
	on a.addid = e.addid
)

-- same as
select distinct addid from employe
where addid not in (select addid from address)

-- nulls can be a problem. If all values are wanted,
-- which is not in the address table then use this:
select addid from employe
where (
	addid not in (select addid from address where addid is not null))
	or
	addid is null
)

-- then there is also this:
-- select de rækker i employe, der ikke er i address tabellen
select e.* from employe e
left join address a on e.addid = a.addid 
where a.addid is null