RaSor’s Blog

October 29, 2009

OLEDB: Create connection string

Filed under: Access, Administration, Databases, SQL — rasor @ 7:20 am

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

June 28, 2009

SQL: Install AdventureWorks

Filed under: Databases, SQL — rasor @ 7:49 am

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:

(more…)

April 23, 2009

SQL: Object Created date

Filed under: SQL — rasor @ 10:28 am

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

November 22, 2008

BarCampCph Nov 2008 Presentation

Filed under: NET, Notes, SQL — rasor @ 2:43 pm

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.  (more…)

October 6, 2008

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

Filed under: Databases, SQL — rasor @ 5:40 am

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)

September 15, 2008

SQL server Backup

Filed under: Databases, SQL — rasor @ 8:00 pm

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. (more…)

SQL server login

Filed under: Databases, SQL — rasor @ 6:02 pm

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

August 12, 2008

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

Filed under: Databases, LINQ, NET, SQL — rasor @ 10:35 am

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. (more…)

May 6, 2008

Update or insert with ID

Filed under: Databases, SQL — rasor @ 6:58 am

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)

April 1, 2008

ReIndex all

Filed under: SQL — rasor @ 11:31 am

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'

February 27, 2008

Unique if not null

Filed under: SQL — rasor @ 10:28 am

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: (more…)

February 21, 2008

System stored procedures and functions

Filed under: SQL — rasor @ 10:43 am

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


January 25, 2008

Print dublets

Filed under: SQL — rasor @ 8:22 am
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

January 24, 2008

If a search string can be null

Filed under: SQL — rasor @ 6:29 am

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

Filed under: SQL — rasor @ 6:26 am
-- 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

Blog at WordPress.com.