Friday, March 23, 2012

How to Create Assembly function using dll files in SQL Server 2005?

Hiiiiiiii all

I have to make a user defined function in c# as the class liberary and create a dll file, now i want to use this function in SQL Server 2005 as a part of CLR Integration

I have tried like this

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

WITH PERMISSION_SET = SAFE

but it gives me

incorrect syntax error

so plzzzzz anyone help me wht to do in my probbbbbbbbb?

Pratik Kansara

What is the exact error-message, and are you sure you are doing it on the SQL 2005 instance (I seem to remember you have both a SQL 2000 as well as 2005 installed)?

Niels
|||
Hiii

yes i have both sql server 2000 and sql server 2005 and .net 2003 and .net 2005 both are installed

Actually there is no any type of group by concate function in sql server 2000- 2005 so tht i have made a class file in c# 2.0
and make a dll file of tht class file

and now sql server 2005 is supporting CLR Integration so tht i want to use tht function in my SQL Server 2005 instead of group by concate, I have already enabled clr enable in my sql server 2005.

but as i think i need to create a assembly function in sql server like

CREATE ASSEMBLY abc
From "c:\abc.dll"

So this statement gives me error so if you have any other option to do this then plz plz help me

thanking youuuuuuuuuuuuuuu...

Pratik Kansara
|||Yes, I understand your problem from your first post. So once again:
1. what is the exact error message you are getting
2. are you sure you are running the CREATE ASSEMBLY statement on the SQL 2005 instance.

The reason I'm asking no 2, is becuase your statement looks OK to me - and the only thing I can think of is that you try to run it on the SQL 2000 instance.

Niels
|||

Hiiiiiii

the exactly error message and syntax is like tht

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

GO

and the error message i m getting is:

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'ASSEMBLY'.

and i have also tried in sql server 2000 instance but as far as i know tht CLR Integration in SQL Server 2000 is not possible.

plz plz give me solution, i need it urgently...

Thnxxxxxxx

Pratik Kansara

|||

Hiiiii

the exact code tht i m writing is and error message is as bellow...

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

GO

and the error message is :

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'ASSEMBLY'.

and i have also tried in SQL Server 2000 instance also but as far as i know tht this is not possible in SQL Server 2000 because SQL Server 2000 does not support CLR Integration

so plzz plzzzz help me i need it urgently

Thnxxxxxxxxx so muchhh

Pratik

|||You are right, CLR integration is not supported in SQL 2000, and that is why I believe that when you run your CREATE ASSEMBLY code that you are logged into the SQL 2000 system, and not SQL 2005.

Couple of more questions:

1. Afe you absolutely sure you are running the statement in the SQL 2005 instance? What is the result from following query:
SELECT @.@.version
2. What tool are you using when you run the CREATE ASSEMBLY statement, Query Analyzer or SQL Server Management Studio? You should be using Management Studio.
3. What database do you try to do this in - is it a new database or?

Niels

|||

Respected Sir,

First of all thanks a lot for replying my mail

Sir, Recently i checked a blog and i going to knew tht there is a compitibility problem in my Sql Server tht u have mension

i have a database name Job which i have made in SQL Server 2005 now while chacking the compitibility using following syntax:

sp_dbcmptlevel Job

it gives me

The current compatibility level is 80.

means tht its a comitibility of SQL Server 2000 and also when i checked the property of my Database then in Option tag i found compatibility Lavel to Microsoft SQL Server 2000(80) and i m not getting here level SQL Server 2005(90)

and after executing the syntax

SELECT @.@.version

i got the message

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and sir I m using Microsoft SQL Server Managment Studio tool for CREATE ASSAMBLY statement

so sir the problem as i think we got is compitibility lavel so sir i have created this database in SQL Server 2005 also

then why i m not getting here compatibility lavel 90 or SQL Server 2005

and sir another thing is tht when i m executing the statement

sp_dbcmptlevel Job, 90

then it gives me following error:

Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92

Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]

Valid values of database compatibility level are 60, 65, 70, or 80.

thnks a lot and plz plz send me this solution.......

once again thnks

|||

Pratik Kansara wrote:

and after executing the syntax

SELECT @.@.version

i got the message

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and sir I m using Microsoft SQL Server Managment Studio tool for CREATE ASSAMBLY statement

so sir the problem as i think we got is compitibility lavel so sir i have created this database in SQL Server 2005 also

then why i m not getting here compatibility lavel 90 or SQL Server 2005

The problem is NOT compatibility levels, if you from SELECT @.@.version are getting as per above SQL Server 2000 - 8.xx then you are NOT working against SQL 2005, you are working against SQL 2000.

Make sure you are connecting to the SQL 2005 instance from Management Studio, as Management Studio allows you to connect both to SQL 2000 as well as SQL 2005.

Niels
|||

Respected Sir

yes i m connected to SQL Server 2000 from the SQL Server 2005 Managment Studio but sir i have cheked the configration tools and other option but i cant find out how to connect to 2005

So will you plz help me how to connect to SQL Server 2005 using Managment Studio ?

thnnking you so much

|||

Respected Sir,

I have successfully solved this problem, and created a assambly in SQL Server thnkin you so much for helping me...

|||Glad you got it to work!

Niels
sql

No comments:

Post a Comment