Freek's Blog

t-sql remove diacritical marks and accents

F. Zomer — Mon, 16 May 2011 12:40:00 GMT

alter FUNCTION fnRemoveAccents(@String VARCHAR(1000))

RETURNS VARCHAR(1000)

BEGIN

SET @String = REPLACE(@String,'','n')

SET @String = REPLACE(@String,'','e')

SET @String = REPLACE(@String,'','o')

SET @String = REPLACE(@String,'','i')

SET @String = REPLACE(@String,'','a')

SET @String = REPLACE(@String,'','y')

SET @String = REPLACE(@String,'','u')

RETURN (SELECT @String)

END

VB6: sending mail by outlook

F. Zomer — Tue, 18 Jan 2011 12:47:00 GMT

create form
Add button
Make a reference to microsoft scripting runtime

Public Function outlookMail(ByVal emailOntvanger As String, ByVal subject As String, ByVal body As String, ByVal bijlage As String) As Boolean

' extra:
' microsoft scripting runtime
' click yes (free software)

On Error GoTo errMsg

Dim ToAddress
Dim MessageSubject
Dim MessageBody
Dim MessageAttachment

Dim ol, ns, newMail

ToAddress = emailOntvanger
MessageSubject = subject
MessageBody = body

Set ol = CreateObject("Outlook.Application")
Set ns = ol.getNamespace("MAPI")
ns.logon "", "", True, False
Set newMail = ol.CreateItem(olMailItem)
newMail.subject = MessageSubject
newMail.body = MessageBody & vbCrLf

newMail.Attachments.Add (bijlage)

' validate the recipient, just in case...
Set myRecipient = ns.CreateRecipient(ToAddress)
myRecipient.Resolve
If Not myRecipient.Resolved Then
MsgBox "unknown recipient"
Else
newMail.Recipients.Add (myRecipient)
newMail.Send
End If

Set ol = Nothing

outlookMail = True

Exit Function
errMsg:

outlookMail = False

End Function

VB6: sending mail by outlook (express)

F. Zomer — Tue, 18 Jan 2011 12:42:00 GMT

'menu: project -> components
'add microsoft MAPI control

'add to the form
'1. MAPISession1
'2. MAPIMessages1
'3. command1 = button

Private Sub Command1_Click()

Call outlookExpressMail("f.zomer@xxx.com", "Onderwerp", "body text", "")

End Sub

Public Sub outlookExpressMail(ByVal RecipAddress As String, ByVal subject As String, ByVal emailBody As String, ByVal attach As String)

Dim s1 As String
Dim b1 As Boolean
Dim filename As String
filename = attach ' filename path

b1 = False
MAPISession1.SignOn
MAPISession1.DownLoadMail = False
If filename <> "" Then
b1 = True
End If
With MAPIMessages1
            .SessionID = MAPISession1.SessionID
            .Compose
            .RecipAddress = RecipAddress        ' email address
            .AddressResolveUI = True
            .ResolveName
            .MsgSubject = subject               ' subject
             If b1 = True Then
            .AttachmentPathName = filename
             End If

            .MsgNoteText = emailBody            ' email body text
            .Send False
End With
MAPISession1.SignOff
'MAPISession1 = Nothing
'MAPIMessages1 = Nothing
End Sub

t-sql: write database to filesystem (excel)

F. Zomer — Tue, 23 Nov 2010 10:29:00 GMT

Dit script haal alle tabellen van een database over naar file system.

voorbereiding:

1. rechten op xp_cmdshell moeten worden geactiveerd.

2. maak op de server een map aan die dezelfde naam heeft als de database.

Bron: http://www.siccolo.com/Articles/SQLScripts/how-to-create-sql-to-mass-export-all-tables-to-excel.html

--1. rechten

--sp_configure 'show advanced options',1

--GO

--RECONFIGURE

--GO

--sp_configure xp_cmdshell,1

--GO

--RECONFIGURE

--het script dat tabellen uitleest en als xls bestand opslaat.

set nocount on

declare @databasename as varchar(255)

set @databasename = 'nedasco_productie_wvp'

declare @table_name varchar(255)

declare @command varchar(7000)

declare @file_name_data varchar(500),

@file_name_header varchar(500),

@path varchar(255)

-- folder for a database already created!!!

set @path = 'c:\' + db_name() + '\'

print @path

declare all_tables cursor for

select name from sysobjects where type = 'U' order by name

for read only

open all_tables

fetch next from all_tables into @table_name

while @@fetch_status=0

begin

set @file_name_data = @path + @table_name + '.xls'

set @file_name_header = @path + @table_name + '_column_info.xls'

--///////////HEADER samenstellen\\\\\\\\\\\\\\\

--1. hoeveel kolom velden zijn er?

declare @aantalheadervelden int

set @aantalheadervelden = (select COUNT(*) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '' + @table_name + '')

print @aantalheadervelden

declare @loop int

declare @regel varchar(4000)

set @loop = 0

set @regel = ''

while @loop < @aantalheadervelden

BEGIN

set @loop = @loop + 1

if @regel = ''

BEGIN

set @regel = (select column_name from INFORMATION_SCHEMA.COLUMNS where ORDINAL_POSITION = @loop and TABLE_NAME = '' + @table_name + '')

END

else

BEGIN

set @regel = @regel + ' ' + (select column_name from INFORMATION_SCHEMA.COLUMNS where ORDINAL_POSITION = @loop and TABLE_NAME = '' + @table_name + '')

END

declare @cmdtxt as varchar(255)

select @cmdtxt = 'echo ' + @regel + ' >> ' + @file_name_header

print @regel

exec master..xp_cmdshell @cmdtxt

set @regel = ''

-- //////////////get data into excel:\\\\\\\\\\\\\\\\\\

set @command = 'bcp ' + db_name() + '.dbo.' + @table_name + ' out ' + @file_name_data + ' -c -t\t -T '

print @command

exec master..xp_cmdshell @command, NO_OUTPUT

--samenvoegen van de header + data bestanden in een txt bestand

select @cmdtxt = 'copy ' + @file_name_header + ' + ' + @file_name_data + ' c:\' + @databasename +'\' + @table_name + '.txt'

print @cmdtxt

exec master..xp_cmdshell @cmdtxt

--verwijderen header en data bestanden

select @cmdtxt = 'del ' + @file_name_header

exec master..xp_cmdshell @cmdtxt

select @cmdtxt = 'del ' + @file_name_data

exec master..xp_cmdshell @cmdtxt

--rename van txt bestand naar xls bestand

select @cmdtxt = 'move c:\' + @databasename + '\' + @table_name + '.txt c:\' + @databasename + '\' + @table_name + '.xls'

print 'copy: ' + @cmdtxt

exec master..xp_cmdshell @cmdtxt

fetch next from all_tables into @table_name

END

close all_tables

deallocate all_tables

set nocount off

t-sql: count rows in tables from 1 database.

F. Zomer — Tue, 16 Nov 2010 11:52:00 GMT

CREATE TABLE #foo

(
tablename VARCHAR(255),
rc INT
)

INSERT #foo
EXEC sp_msForEachTable
'SELECT PARSENAME(''?'', 1), COUNT(*) FROM ?'
SELECT tablename, rc
FROM #foo
ORDER BY 1 asc

t-sql: return value from stored procedure

F. Zomer — Thu, 04 Nov 2010 10:00:00 GMT

method1

create procedure ProdTest @trigger as int, @test varchar(15) output
as
begin
if @trigger = 0
begin
set @test = '0 result'
end
else
begin
set @test = '1 result'
end
end

----------------------

declare @getOuputValue as varchar(20)
execute dbo.ProdTest 0,@getOuputValue output
select @getOuputValue

----------------------

0 result

method2

ALTER PROCEDURE test99 (@input varchar(10))
AS
BEGIN
SET NOCOUNT ON;
select @input, @input
END
GO

------

exec test99 'halo'

---------

(No column name) (No column name)
halo halo

c#: thread simple sample

F. Zomer — Mon, 01 Nov 2010 14:04:00 GMT

Thread tZH = new Thread(new ThreadStart(ZH308));
tZH.IsBackground = true;
tZH.Start();

Thread tHA = new Thread(new ThreadStart(HA304));
tHA.IsBackground = true;
tHA.Start();

----

void ZH308()
{ GetBerichtSoort("blabla"); }

void HA304()
{ GetBerichtSoort("blabla"); }

t-sql: how to use a variable in a TOP command and get random records

F. Zomer — Tue, 24 Aug 2010 13:56:00 GMT

SELECT TOP X COLUMNS FROM TABLE

X = variable

Assignment: show only 5 percent of a given range.....

Solution:

declare @cntRows int

set @cntRows = (select count(*) from tablename where date = getdate()) --set total

set @cntRows = (@cntRows * 0.05) --set 5 percent

select top (@cntRows ) * from tablename where date = getdate() order by newid() --get 5 percent of random records!!!!!

t-sql: how to exit a stored procedure

F. Zomer — Thu, 15 Jul 2010 16:38:00 GMT

Hi, I want to exit a stored procure when certain conditions are true.
You can use stored proc the RETURN statement for that.
.
..
...

DECLARE @CHECK001 AS INT
SET @CHECK001 = (SELECT COUNT(*) FROM tablename) -->create condition

if (@CHECK001 = 0) -->check condition
BEGIN
PRINT 'There are no records to process!'
RETURN -->exit the stored proc from here
END

...
..
.

t-sql: how to call a sql stored procedure from msaccess

F. Zomer — Thu, 08 Jul 2010 16:57:00 GMT

How to call a ms-sql stored procedure from MsAccess?

Here i use also the parameters (emailadres and some_text)

1. start a msaccess application.
2. create a button a a form and paste the next code to it.

Dim emailadres As String
emailadres = InputBox("Uw emailadres is:... ")

    If len(emailadres) <> 0 Then
        test (emailadres)
    End If

3. next create a new module and past the next code to it.

Sub test(emailadres)

Dim cmd As New ADODB.Command

Dim param As New ADODB.Parameter
With param
     .Type = adChar
     .Size = 100
     .Value = emailadres
   cmd.Parameters.Append param
End With

Dim param2 As New ADODB.Parameter
With param2
    .Type = adChar
    .Size = 100
    .Value = "some text"
cmd.Parameters.Append param2
End With

cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=servername;Initial Catalog=databasename;UID=loginname;PWD=password;"
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = emailadres
cmd.Execute

End Sub

4. goto sql server and add a stored procedure to it. Like this one:

CREATE PROCEDURE [dbo].[emailMe]
(
@emailadres as varchar(10) --param
,@onderwerp as varchar(50) --param2
)

AS
BEGIN

EXEC master.dbo.xp_sendmail
@recipients = @emailadres
, @subject = @onderwerp
END

and voila! Sending a email initiated from msaccess.

t-sql: avoiding problems when using a MSDAQL query

F. Zomer — Wed, 07 Jul 2010 10:05:00 GMT

Running my query gives the next error statement.

The expected data length is 5, while the returned data length is 1

or in other words

'MSDASQL' returned an unexpected data length for the fixed-length column

By using: DBCC TRACEON(8765)
As first line in my query was the solution.
Don't know why but it works!!

Example:

DBCC TRACEON(8765)
Select * from openquery(Transoft, ' select field1, field2 from table)

Transsoft = driver I use

t-sql: [The data value violated the integrity constraints for the column.]

F. Zomer — Mon, 05 Jul 2010 15:52:00 GMT

I was inserting my datetime values from sql server 2000 to a column in a sql server 2008 database.
The result was:

OLE DB provider 'SQLOLEDB' could not INSERT INTO table '[...]' because of column '...'. The data value violated the integrity constraints for the column.

This was a not nullable field. Because there is (I verified this) always a filled field.

When I made it nullable, the insert processed with no problems.
And ALL the fields where filled with datetime values.

Is this a bug?

or am I missing a conversion here?

t-sql: How to store results using the function sp_columns

F. Zomer — Mon, 05 Jul 2010 09:59:00 GMT

I needed to know what kind of datatype a certain column was.
By opening the table in object explorer is one way to do it.

I found another (easier?) way of doing it.

1. first create and empty temp table
2. fill this table with the desired table using sp_columns
3. retrieve the values.

--create table

create table #ColumnInfo(
TABLE_QUALIFIER sysname null,
TABLE_OWNER sysname null,
TABLE_NAME sysname null,
COLUMN_NAME sysname null,
DATA_TYPE sysname null,
TYPE_NAME sysname null,
[PRECISION] int null,
LENGTH int null,
SCALE int null,
RADIX int null,
NULLABLE bit null,
REMARKS nvarchar(4000) Null,
COLUMN_DEF sysname null,
SQL_DATA_TYPE int null,
SQL_DATETIME_SUB int null,
CHAR_OCTET_LENGTH int null,
ORDINAL_POSITION int null,
IS_NULLABLE char(3) null,
SS_DATA_TYPE int null)

--fill table

insert into #ColumnInfo
exec sp_columns 'table_name'

--retrieve values

select column_name, type_name, [precision], length, nullable
from #ColumnInfo where column_name like '%column_name%'

example result:
column_name type_name precision length nullable
ID int identity 10 4 0
etc

t-sql: how tu use the 'current date' in a MSDASQL query

F. Zomer — Thu, 01 Jul 2010 11:00:00 GMT

I have a queries which retrieves invoices from a table.
But the getdate() is not an option in this case, because I use a ODBC driver.

When I use the below function there is no problem.

select {fn NOW()}

But this shows also the time.
By using

SELECT {fn curdate()}

I was able to avoid the time part.

My query looked like this

Select top 10 * from openquery(Transoft, '
select * from table
WHERE processDate = {fn curdate()}')

Transsoft = ODBC driver to connect to tables I us.

tsql: how to check if a temp table exists and drop it if it does.

F. Zomer — Thu, 10 Jun 2010 16:21:00 GMT

How to determine if a temp table already exists? Well, I found this sql script that works fine for me!
If the temptable already exists it will be dropped by the last line in the code.

if exists (
select * from tempdb.dbo.sysobjects o
where o.xtype in ('U')
and o.id = object_id(N'tempdb..#tablename')
)
drop table #tablename

t-sql: returning table count from a variable tablename using sp_executesql command and pass it to a variable

F. Zomer — Thu, 10 Jun 2010 13:25:00 GMT

In this case I wanted to get the record count of a table and pass this number to another table for reporting services.
But how to get the number of records into a variable?

Well by using executesql procedure.
This procedure is divided into 3 parts:
1. the command with parameter(s)
2. the parameters = contains the values mentioned in the command
3. the values = contains the returning values

see also: h t t p ://www.databasejournal.com/features/mssql/article.php/3286501/T-SQL-Programming-Part-4---Setting-Variables-in-Calling-T-SQL-Code-While-Using-spexecutesql.htm

the code I used was:

declare @tblName as varchar(20)
set @tblName = 'tablename'
declare @RECCNT int
declare @CMD Nvarchar(100)

SET @CMD = 'SELECT @RECORDCNT=count(*) from ' + @tablename

print @CMD

exec sp_executesql @CMD,
N'@RECORDCNT int out',
@RECCNT out

print @reccnt

result:

SELECT @RECORDCNT=count(*) from tablename

97954

T-SQL: select random records from table

F. Zomer — Thu, 03 Jun 2010 09:20:00 GMT

A nice way to select random records from a table is the use of NEWID().

Example:

Select TOP 5 * from [tablename] ORDER BY NEWID()

When run the above command twice, you will see that the output differs when you have more than 5 records, of course.

C#: Collect quickly directory names and loop through them

F. Zomer — Wed, 12 May 2010 12:32:00 GMT

//collect directory names and put them in an array
string[] mapnaam = System.IO.Directory.GetDirectories(@"C:\");

//loop through the array
foreach (string map in mapnaam)
{
//for each file found in the directory with the extension 'txt'
foreach (string filename in System.IO.Directory.GetFiles(map, "*.txt", System.IO.SearchOption.TopDirectoryOnly))
{
do something...
}
}

C#: using console colors

F. Zomer — Wed, 12 May 2010 12:27:00 GMT

Example:

Console.BackgroundColor = ConsoleColor.Blue;

Console.ForegroundColor = ConsoleColor.White;

Other colors are:(the colors are not exact, only an indication of it)

             Black
             DarkBlue
             DarkGreen
             DarkCyan
             DarkRed
             DarkMagenta
             DarkYellow
             Gray
             DarkGray
             Blue
             Green
             Cyan
             Red
             Magenta
             Yellow
             White

C#: using the sortedlist

F. Zomer — Mon, 10 May 2010 17:01:00 GMT

System.Collections.Generic.SortedDictionary<string, long> Dic = new SortedDictionary<string, long>();

.
..
...
....
...
..
.

Collect some key's and there values

//contains list already the key value (here represented by: ext)?
if (Dic.Keys.Contains(ext))
{
//found in list then add 1 to the value.
long i = Dic[ext]; // get the value for the key
i++;//add 1
Dic[ext] = i;//update value
}
else
{
//not? then add this key and set default value to 1.
Dic.Add(ext, 1);
}

.
..
...
....
...
..
.

Read out the sortedlist

//print dictionary e.g. to screen

foreach (var pair in Dic)
{
this.textBox1.AppendText(pair.ToString() + "\r\n");
Application.DoEvents();
}

//remove all entries
Dic.Clear();

Output will be like

[the key, the value]
[the key, the value]
[the key, the value]
[the key, the value]
etc, etc..

C#: function to write text to file

F. Zomer — Tue, 04 May 2010 15:56:00 GMT

//write value to given file
//value is the text to write
//path is the location of the text file

public void funcWriter(string value, string path)

{

using (System.IO.StreamWriter sw = new System.IO.StreamWriter(path, true))

{

sw.WriteLine(value);

sw.Flush();

}

C#: store all files from a directory in an array

F. Zomer — Tue, 04 May 2010 15:54:00 GMT

//read all files in directory and store them

string[] array1 = System.IO.Directory.GetFiles(lblPath.Text);

//process each file

foreach (string s in array1)

{

//do something

}

C#: read text file line by line

F. Zomer — Tue, 04 May 2010 10:02:00 GMT

using System;
using System.IO;

private void button1_Click(object sender, EventArgs e)

{

string path = @"c:\temp\MyTest.txt";

try

{

using ( System.IO.StreamReader sr = new System.IO.StreamReader(path))

{

while (sr.Peek() >= 0)

{

Console.WriteLine(sr.ReadLine());

}

catch (Exception)

{

Console.WriteLine("The process failed: {0}", e.ToString());

}

C#: folderbrowserdialog

F. Zomer — Tue, 04 May 2010 09:48:00 GMT

private void btnDirectory_Click(object sender, EventArgs e)

{

FolderBrowserDialog folderBrowserDialog1 = new FolderBrowserDialog();

string folderName="";

DialogResult result = folderBrowserDialog1.ShowDialog();

if (result == DialogResult.OK)

{

folderName = folderBrowserDialog1.SelectedPath;

}

Console.WriteLine("Selected path: {0}",folderName);

}

The weather forecast

F. Zomer — Tue, 04 May 2010 08:45:00 GMT