Problem freeing resources used by SQLPrimaryKeys

Hello all,
First of all, I'm a little inexperienced with the odbc32.dll API's and
not too familiar with all the details of their usage. I have a list of
tables in the database and I'm iterating over that list and building
the primary key for each table. When I perform the iteration my
machine's memory usage spikes and the memory is never released (until
the program ends, of course). If I run the process several times the
memory usage becomes so high that the program crashes. I know that it's
not from creating objects in the application because I removed the code
that instantiates them and it still performed the same. Basically, I
reduced the algorithm to just the code that uses the odbc32.dll API's.
Below is the general code I'm using for each table in one iteration.
Can anyone tell me what I'm doing wrong and if there's any way to
correct it?
Thanks in advance,
Shannon
// Declare all the variables
res = SQLAllocEnv( ref env );
res = SQLAllocConnect( env, ref hdbc );
res = SQLConnect( hdbc.ToInt32(), dsnName, SQL_NTS, user, ( short
)( user == null ? 0 : user.Length ), pwd, ( short )( pwd == null ? 0 :
pwd.Length ) );
res = SQLAllocHandle( SQL_HANDLE_STMT, hdbc.ToInt32(), ref hstmt
);
res = SQLPrimaryKeys( hstmt.ToInt32(), null, SQL_NTS, schema, (
short )( schema == null ? 0 : schema.Length ), tableName, ( short
)tableName.Length );
res = SQLFetch( hstmt.ToInt32() );
while( res == 0 )
{
SQLGetData( hstmt.ToInt32(), 1, SQL_CHAR, szCatalog,
MAX_FIELDSIZE, ref lCatalog );
SQLGetData( hstmt.ToInt32(), 2, SQL_CHAR, szSchema,
MAX_FIELDSIZE, ref lSchema );
SQLGetData( hstmt.ToInt32(), 3, SQL_CHAR, szTableName,
MAX_FIELDSIZE, ref lTableName );
SQLGetData( hstmt.ToInt32(), 4, SQL_CHAR, szColumnName,
MAX_FIELDSIZE, ref lColumnName );
SQLGetData( hstmt.ToInt32(), 5, SQL_SMALLINT, szColumnSequence,
MAX_FIELDSIZE, ref lColumnSequence );
SQLGetData( hstmt.ToInt32(), 6, SQL_CHAR, szPKName,
MAX_FIELDSIZE, ref lPKName );
// Create application objects from primary key data
res = SQLFetch( hstmt.ToInt32() );
}
res = SQLCloseCursor( hstmt.ToInt32() );
res = SQLFreeHandle( SQL_HANDLE_STMT, hstmt.ToInt32() );
res = SQLFreeHandle( SQL_HANDLE_DBC, hdbc.ToInt32() );
res = SQLFreeHandle( SQL_HANDLE_ENV, env );
res = SQLDisconnect( hdbc.ToInt32() );Okay, after all the hair pulling, wouldn't it figure that the minute I
post a question I figure it out. In the cleanup process, I call
SQLFreeHandle on hdbc before I call SQLDisconnect on it. You must
disconnect first.

Problem Creating Stored Procedure From A Library, DLL

I'm having a problem creating Stored Procedure from a library I have created. I have found that the Create Assembly query causes the Assemby to be placed into the Master Database's Assembly collection. The other is that the Create Procedure Query states that it cannot find the type in the assembly. Can someone see what is going on and let me know

I have also changed the "struct" to a "class" and I get the same error message.

I've included the code snippit, and queries belows:

using System;
using System.Collections.Generic;
using System.Text;

using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace HHLibrary
{
[Serializable]
public struct State
{
[SqlFunction(Name = "GetStateIndexByName",
IsDeterministic = true,
DataAccess = DataAccessKind.Read)]
public static SqlInt32 GetStateIndexByName(SqlString Name, out SqlByte StateIndex)
{
SqlInt32 iResult = 0;
StringBuilder sb = new StringBuilder(500);
SqlCommand command = new SqlCommand();

StateIndex = 255; // Set the output parameter to an illegal value

. . .

return iResult;
}

}
}

************************ Compilation Messages ******************************************

Build started: Project: HeadHunterLibrary, Configuration: Debug Any CPU
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Csc.exe /noconfig /nowarn:1701,1702 /errorreport:prompt /warn:4 /define:DEBUG;TRACE /reference:C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /debug+ /debug:full /optimize- /out:obj\Debug\HeadHunterLibrary.dll /target:library State.cs Properties\AssemblyInfo.cs

Compile complete -- 0 errors, 0 warnings
HeadHunterLibrary -> C:\HHLibraries\HeadHunterLibrary.dll
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

************************ Trans-SQL Query ******************************************

create assembly HeadHunterLibrary
FROM 'C:\HHLibraries\HeadHunterLibrary.dll'
WITH PERMISSION_SET = SAFE;
GO

CREATE PROCEDURE dbo.GetStateIndexByName
( @.Name nvarchar(25),
@.StateIndex tinyint
)
AS EXTERNAL NAME HeadHunterLibrary.State.GetStateIndexByName;
GO

************************ Messages From Executing The Query ************************************

Msg 6505, Level 16, State 1, Procedure GetStateIndexByName, Line 2
Could not find Type 'State' in assembly 'HeadHunterLibrary'.

You need to specify the database in use. CREATE ASSEMBLY add assemblies to the database in use. For example:

use MyDatabase
create assembly HeadHunterLibrary
FROM 'C:\HHLibraries\HeadHunterLibrary.dll'
WITH PERMISSION_SET = SAFE;
GO

You can double check that it worked by:

SELECT * from myDatabase.sys.assemblies

You need to square bracket the namespace.class. As was written thinks HeadHunterLibrary is a database name. For example:

AS EXTERNAL NAME [HeadHunterLibrary.State].GetStateIndexByName;

Dan

|||

Made the corrections you recommended. Still have one problem that I don’t understand. The query I execute to create the stored procedure is:

USE HeadHunter

CREATE PROCEDURE dbo.GetStateIndexByName

( @.Name nvarchar(25),

@.StateIndex tinyint OUTPUT

)

AS EXTERNAL NAME [HeadHunterLibrary.State].GetStateIndexByName;

When I execute the Create Procedure dbo.GetStateIndexByName I get the following error message.

Msg 102, Level 15, State 1, Procedure GetStateIndexByName, Line 5 Incorrect syntax near ';'.

The managed code looks like this:

namespace HeadHunterLibrary

{

[Serializable]

public class State

{

public static SqlInt32 GetStateIndexByName(SqlString Name, out SqlByte StateIndex)

{

. . .

}

}

}

Can you see what is causing the error?

Thank You

Dan Marks

Logos-Systems@.mchsi.com

|||

Made the corrections you recommended. Still have one problem that I don’t understand. The query I execute to create the stored procedure is:

USE HeadHunter

CREATE PROCEDURE dbo.GetStateIndexByName

( @.Name nvarchar(25),

@.StateIndex tinyint OUTPUT

)

AS EXTERNAL NAME [HeadHunterLibrary.State].GetStateIndexByName;

When I execute the Create Procedure dbo.GetStateIndexByName I get the following error message.

Msg 102, Level 15, State 1, Procedure GetStateIndexByName, Line 5 Incorrect syntax near ';'.

The managed code looks like this:

namespace HeadHunterLibrary

{

[Serializable]

public class State

{

public static SqlInt32 GetStateIndexByName(SqlString Name, out SqlByte StateIndex)

{

. . .

}

}

}

Can you see what is causing the error?

Thank You

Dan Marks

Logos-Systems@.mchsi.com

|||

Made the corrections you recommended. Still have one problem that I don’t understand. The query I execute to create the stored procedure is:

USE HeadHunter

CREATE PROCEDURE dbo.GetStateIndexByName

( @.Name nvarchar(25),

@.StateIndex tinyint OUTPUT

)

AS EXTERNAL NAME [HeadHunterLibrary.State].GetStateIndexByName;

When I execute the Create Procedure dbo.GetStateIndexByName I get the following error message.

Msg 102, Level 15, State 1, Procedure GetStateIndexByName, Line 5 Incorrect syntax near ';'.

The managed code looks like this:

namespace HeadHunterLibrary

{

[Serializable]

public class State

{

public static SqlInt32 GetStateIndexByName(SqlString Name, ref SqlByte StateIndex)

{

. . .

}

}

}

Can you see what is causing the error?

Thank You

Dan Marks

Logos-Systems@.mchsi.com

|||

I see several problems.

The error is probably caused by the lack of assembly name in:

AS EXTERNAL NAME [HeadHunterLibrary.State].GetStateIndexByName;

Try this:

AS EXTERNAL NAME [AssemblyName].[HeadHunterLibrary.State].GetStateIndexByName;

You are also missing a return value (your procedure returns a SqlInt32, but you don't specify that in the stored procedure definition).

Finally, you don't have a StoredProcedure attribute on your method, put this on the line before the method:

[Microsoft.SqlServer.Server.SqlProcedure]

Hope this helps.

|||

Namespace problem?

CREATE PROCEDURE hello

AS

EXTERNAL NAME HelloWorld.StoredProcedures.HelloWorld

GO

EXEC hello

Msg 6505, Level 16, State 1, Procedure hello, Line 1

Could not find Type 'StoredProcedures' in assembly 'HelloWorld'.

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure 'hello'.

CREATE PROCEDURE hello

AS

EXTERNAL NAME HelloWorld.[HelloWorld.StoredProcedures].HelloWorld

GO

EXEC hello

Hello world! It's now 18/04/2007 14:26:23

try something like:

CREATE PROCEDURE dbo.GetStateIndexByName
( @.Name nvarchar(25),
@.StateIndex tinyint
)
AS EXTERNAL NAME HeadHunterLibrary.[HHLibrary.State].GetStateIndexByName;
GO

Problem Creating Stored Procedure From A Library, DLL

I'm having a problem creating Stored Procedure from a library I have created. I have found that the Create Assembly query causes the Assemby to be placed into the Master Database's Assembly collection. The other is that the Create Procedure Query states that it cannot find the type in the assembly. Can someone see what is going on and let me know

I have also changed the "struct" to a "class" and I get the same error message.

I've included the code snippit, and queries belows:

using System;
using System.Collections.Generic;
using System.Text;

using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace HHLibrary
{
[Serializable]
public struct State
{
[SqlFunction(Name = "GetStateIndexByName",
IsDeterministic = true,
DataAccess = DataAccessKind.Read)]
public static SqlInt32 GetStateIndexByName(SqlString Name, out SqlByte StateIndex)
{
SqlInt32 iResult = 0;
StringBuilder sb = new StringBuilder(500);
SqlCommand command = new SqlCommand();

StateIndex = 255; // Set the output parameter to an illegal value

. . .

return iResult;
}

}
}

************************ Compilation Messages ******************************************

Build started: Project: HeadHunterLibrary, Configuration: Debug Any CPU
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Csc.exe /noconfig /nowarn:1701,1702 /errorreport:prompt /warn:4 /define:DEBUG;TRACE /reference:C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /debug+ /debug:full /optimize- /out:obj\Debug\HeadHunterLibrary.dll /target:library State.cs Properties\AssemblyInfo.cs

Compile complete -- 0 errors, 0 warnings
HeadHunterLibrary -> C:\HHLibraries\HeadHunterLibrary.dll
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

************************ Trans-SQL Query ******************************************

create assembly HeadHunterLibrary
FROM 'C:\HHLibraries\HeadHunterLibrary.dll'
WITH PERMISSION_SET = SAFE;
GO

CREATE PROCEDURE dbo.GetStateIndexByName
( @.Name nvarchar(25),
@.StateIndex tinyint
)
AS EXTERNAL NAME HeadHunterLibrary.State.GetStateIndexByName;
GO

************************ Messages From Executing The Query ************************************

Msg 6505, Level 16, State 1, Procedure GetStateIndexByName, Line 2
Could not find Type 'State' in assembly 'HeadHunterLibrary'.

You need to specify the database in use. CREATE ASSEMBLY add assemblies to the database in use. For example:

use MyDatabase
create assembly HeadHunterLibrary
FROM 'C:\HHLibraries\HeadHunterLibrary.dll'
WITH PERMISSION_SET = SAFE;
GO

You can double check that it worked by:

SELECT * from myDatabase.sys.assemblies

You need to square bracket the namespace.class. As was written thinks HeadHunterLibrary is a database name. For example:

AS EXTERNAL NAME [HeadHunterLibrary.State].GetStateIndexByName;

Dan

|||

Made the corrections you recommended. Still have one problem that I don’t understand. The query I execute to create the stored procedure is:

USE HeadHunter

CREATE PROCEDURE dbo.GetStateIndexByName

( @.Name nvarchar(25),

@.StateIndex tinyint OUTPUT

)

AS EXTERNAL NAME [HeadHunterLibrary.State].GetStateIndexByName;

When I execute the Create Procedure dbo.GetStateIndexByName I get the following error message.

Msg 102, Level 15, State 1, Procedure GetStateIndexByName, Line 5 Incorrect syntax near ';'.

The managed code looks like this:

namespace HeadHunterLibrary

{

[Serializable]

public class State

{

public static SqlInt32 GetStateIndexByName(SqlString Name, out SqlByte StateIndex)

{

. . .

}

}

}

Can you see what is causing the error?

Thank You

Dan Marks

Logos-Systems@.mchsi.com

|||

Made the corrections you recommended. Still have one problem that I don’t understand. The query I execute to create the stored procedure is:

USE HeadHunter

CREATE PROCEDURE dbo.GetStateIndexByName

( @.Name nvarchar(25),

@.StateIndex tinyint OUTPUT

)

AS EXTERNAL NAME [HeadHunterLibrary.State].GetStateIndexByName;

When I execute the Create Procedure dbo.GetStateIndexByName I get the following error message.

Msg 102, Level 15, State 1, Procedure GetStateIndexByName, Line 5 Incorrect syntax near ';'.

The managed code looks like this:

namespace HeadHunterLibrary

{

[Serializable]

public class State

{

public static SqlInt32 GetStateIndexByName(SqlString Name, out SqlByte StateIndex)

{

. . .

}

}

}

Can you see what is causing the error?

Thank You

Dan Marks

Logos-Systems@.mchsi.com

|||

Made the corrections you recommended. Still have one problem that I don’t understand. The query I execute to create the stored procedure is:

USE HeadHunter

CREATE PROCEDURE dbo.GetStateIndexByName

( @.Name nvarchar(25),

@.StateIndex tinyint OUTPUT

)

AS EXTERNAL NAME [HeadHunterLibrary.State].GetStateIndexByName;

When I execute the Create Procedure dbo.GetStateIndexByName I get the following error message.

Msg 102, Level 15, State 1, Procedure GetStateIndexByName, Line 5 Incorrect syntax near ';'.

The managed code looks like this:

namespace HeadHunterLibrary

{

[Serializable]

public class State

{

public static SqlInt32 GetStateIndexByName(SqlString Name, ref SqlByte StateIndex)

{

. . .

}

}

}

Can you see what is causing the error?

Thank You

Dan Marks

Logos-Systems@.mchsi.com

|||

I see several problems.

The error is probably caused by the lack of assembly name in:

AS EXTERNAL NAME [HeadHunterLibrary.State].GetStateIndexByName;

Try this:

AS EXTERNAL NAME [AssemblyName].[HeadHunterLibrary.State].GetStateIndexByName;

You are also missing a return value (your procedure returns a SqlInt32, but you don't specify that in the stored procedure definition).

Finally, you don't have a StoredProcedure attribute on your method, put this on the line before the method:

[Microsoft.SqlServer.Server.SqlProcedure]

Hope this helps.

|||

Namespace problem?

CREATE PROCEDURE hello

AS

EXTERNAL NAME HelloWorld.StoredProcedures.HelloWorld

GO

EXEC hello

Msg 6505, Level 16, State 1, Procedure hello, Line 1

Could not find Type 'StoredProcedures' in assembly 'HelloWorld'.

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure 'hello'.

CREATE PROCEDURE hello

AS

EXTERNAL NAME HelloWorld.[HelloWorld.StoredProcedures].HelloWorld

GO

EXEC hello

Hello world! It's now 18/04/2007 14:26:23

try something like:

CREATE PROCEDURE dbo.GetStateIndexByName
( @.Name nvarchar(25),
@.StateIndex tinyint
)
AS EXTERNAL NAME HeadHunterLibrary.[HHLibrary.State].GetStateIndexByName;
GO

Problem creating a function from an assembly

I have compiled a dll with the following code:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes

Public Class SICTrans

Public Shared Function TransSIC(ByVal inpSIC As String) As String
'Dim conn As SqlConnection = New SqlConnection("context connection=true")
Dim NewSIC, TempSIC, tempFSIC As String

If Len(NZ(inpSIC)) > 0 Then
TempSIC = NZ(inpSIC)

If Len(TempSIC) < 5 Then TempSIC = Left("00000", 5 - Len(TempSIC))

tempFSIC = Left(TempSIC, 2) + "." + Mid(TempSIC, 3, 2)

If Val(Right(TempSIC, 1)) > 0 Then

tempFSIC = tempFSIC + "/" + Right(TempSIC, 1)
End If
End If

NewSIC = tempFSIC

TransSIC = NewSIC

End Function
Public Shared Function NZ(ByVal input As String) As String
If Not (input Is Nothing) Then
Return input
Exit Function
End If
Return String.Empty

End Function
End Class

Which compiles fine...

i then use the following code to create the assembly in SQL which is fine:

USE NARD
GO
CREATE ASSEMBLY SICCodeTrans
FROM 'c:\SICCodeTrans.dll'
WITH PERMISSION_SET = SAFE
GO

but when i goto create the function with the following code it wont have it!

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.SICTrans.TransSIC
GO

It gives me the following error message

Msg 6505, Level 16, State 1, Procedure TransSICCode, Line 1
Could not find Type 'SICTrans' in assembly 'SICCodeTrans'.

Any ideas?

Thanks

Marek Kluczynski

I think what's going on here is that there is a root namespace on the project.

IIRC, in a C# project VS will simply insert the root namespace delcaration into your code when you use certain templates. In VB, on the other hand, the compiler decides to insert the namespace definition without having it show up in the code.

When you go to create the function in SQL, it can't find it because there is no SICTrans class in the default namespace, there is a SICTrans class in whatever root namespace was set on the project.

You can right-click on the project in VS and pull up the propertiesunder the "Application" tab you'll see this.

Assuming your namespace is NAMESPACE, you'd think you could do the following:

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.NAMESPACE.SICTrans.TransSIC
GO

This won't workit's a (rather annoying) syntax error. You have to write it like this:

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.[NAMESPACE.SICTrans].TransSIC
GO

Hope this works for you. Let us know. :)

Cheers
-Isaac

|||

yes this seems to have resolved the namespace error.

I now have the following error:

Msg 6551, Level 16, State 2, Procedure TransSICCode, Line 1

CREATE FUNCTION for "TransSICCode" failed because T-SQL and CLR types for return value do not match.

Many thanks

Marek

|||I suspect two things. First, you should map the string to an nvarchar, not a varcharCLR strings are Unicode. Second, you need to give a parameter to the nvarchar, e.g., nvarchar(4000), nvarchar(max), etc.

Cheers,
-Isaac|||Many thanks for all your help

Problem creating a function from an assembly

I have compiled a dll with the following code:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes

Public Class SICTrans

Public Shared Function TransSIC(ByVal inpSIC As String) As String
'Dim conn As SqlConnection = New SqlConnection("context connection=true")
Dim NewSIC, TempSIC, tempFSIC As String

If Len(NZ(inpSIC)) > 0 Then
TempSIC = NZ(inpSIC)

If Len(TempSIC) < 5 Then TempSIC = Left("00000", 5 - Len(TempSIC))

tempFSIC = Left(TempSIC, 2) + "." + Mid(TempSIC, 3, 2)

If Val(Right(TempSIC, 1)) > 0 Then

tempFSIC = tempFSIC + "/" + Right(TempSIC, 1)
End If
End If

NewSIC = tempFSIC

TransSIC = NewSIC

End Function
Public Shared Function NZ(ByVal input As String) As String
If Not (input Is Nothing) Then
Return input
Exit Function
End If
Return String.Empty

End Function
End Class

Which compiles fine...

i then use the following code to create the assembly in SQL which is fine:

USE NARD
GO
CREATE ASSEMBLY SICCodeTrans
FROM 'c:\SICCodeTrans.dll'
WITH PERMISSION_SET = SAFE
GO

but when i goto create the function with the following code it wont have it!

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.SICTrans.TransSIC
GO

It gives me the following error message

Msg 6505, Level 16, State 1, Procedure TransSICCode, Line 1
Could not find Type 'SICTrans' in assembly 'SICCodeTrans'.

Any ideas?

Thanks

Marek Kluczynski

I think what's going on here is that there is a root namespace on the project.

IIRC, in a C# project VS will simply insert the root namespace delcaration into your code when you use certain templates. In VB, on the other hand, the compiler decides to insert the namespace definition without having it show up in the code.

When you go to create the function in SQL, it can't find it because there is no SICTrans class in the default namespace, there is a SICTrans class in whatever root namespace was set on the project.

You can right-click on the project in VS and pull up the propertiesunder the "Application" tab you'll see this.

Assuming your namespace is NAMESPACE, you'd think you could do the following:

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.NAMESPACE.SICTrans.TransSIC
GO

This won't workit's a (rather annoying) syntax error. You have to write it like this:

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.[NAMESPACE.SICTrans].TransSIC
GO

Hope this works for you. Let us know. :)

Cheers
-Isaac

|||

yes this seems to have resolved the namespace error.

I now have the following error:

Msg 6551, Level 16, State 2, Procedure TransSICCode, Line 1

CREATE FUNCTION for "TransSICCode" failed because T-SQL and CLR types for return value do not match.

Many thanks

Marek

|||I suspect two things. First, you should map the string to an nvarchar, not a varcharCLR strings are Unicode. Second, you need to give a parameter to the nvarchar, e.g., nvarchar(4000), nvarchar(max), etc.

Cheers,
-Isaac|||Many thanks for all your help