Freek's Blog

alter FUNCTION fnRemoveAccents(@String VARCHAR(1000))

RETURNS VARCHAR(1000)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

RETURN (SELECT @String)

create form
Add button
Make a reference to microsoft scripting runtime

a

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

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

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

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

End Sub

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

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

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

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

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

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

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

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

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

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

----

SELECT TOP X COLUMNS FROM TABLE

X = variable

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

Solution:

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

...
..
.

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
)

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

and voila! Sending a email initiated from msaccess.

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

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

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?

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

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.

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

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:

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.

see also: http://msdn.microsoft.com/en-us/library/ms190348.aspx

//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...
}
}

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

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);
}

//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();

            }

        }

            //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

            }

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());

            }

        }

       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);

        }