How to add reference in C# SQL Server Project (June CTP)

Hi,

I'm using June CTP Visual studio .NET Framework. I have created SQL Server Project in C# language. In that i want to add external dll's to that Project. But when i click the Add reference in the References, it shows only two Tabs 1. Projects 2. SQL Server.

I cant able to add external dll's located in some other location to my References. How to do this? Please let me know if anybody know the solution.

Thanks,
Vinoth
Vinoth@.gsdindia.com

SQL Server projects in Visual Studio 2005 allow you to add references to other SQL Server projects and assemblies registered in the database you are connected to. Apart from these you cannot add references to assemblies on disk.
If you need to use an assembly, register it inside SQL Server using CREATE ASSEMBLY statement from SQL Server Management Studio (refer SQL Server Books Online for more help) and then you would see this assembly in the SQL Server tab.
A question: What are the external dll's you want to use? Are they developed by you or are they coming from somewhere else?

Thanks,
-Vineet.|||

Thanks for ur reply,

A question: What are the external dll's you want to use? Are they developed by you or are they coming from somewhere else?

Yes Vineet,

The external dll's are developed by me.It's not related to SQL server Project. Can i register the external dll's(eventhough its not related to SQL Server Project) using CREATE ASSEMBLY statement from SQL Server Management Studio and can i use it?

Thanks,
Vinoth

How to add CLR dll after restore db on another server? thanks

I use VS2005 to deploy my CLR dll, it is easy. But after I copy db over to another server, the new server doesn't have driver W, so I need put dlls on Driver C. I think I have to relink assembly with those dlls. How should I do that, I tried Alter Assembly add file, but it doesn't work, the following is a script I get from one of my assembly. The dll is emaillib.dll, I could not find it in script. I have copied the dll to c:\emaillib.dll, so how can I link it back? I don;t want to drop the assembly since if I do that, I have to drop all CLR objects of the assembly first.

CREATE ASSEMBLY [EmailLib]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000......

WITH PERMISSION_SET = UNSAFE

GO
ALTER ASSEMBLY [EmailLib]
ADD FILE FROM 0x4D6963726F736F667420432F432B2B204D534620372E30300D0A1A445300000000020000020000000F00000048000000000000000D000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C0FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF38C0FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF.... bery long

AS N'EmailLib.pdb'

GO
ALTER ASSEMBLY [EmailLib]
ADD FILE FROM 0xEFBBBF7573696E672053797374656D2E5265666C656374696F6E3B0D0A7573696E672053797374656D2E52756E74696D652E436F6D70696C657253657276696365733B0D0A7573696E672053797374656D2E52756E74696D652E496E7465726F7053657276696365733B0D0A7573696E672053797374656D2E446174612E53716C3B0D0A0D0A2F2F2047656E6572616C20496E666F726D6174696F6E2061626F757420616E20617373656D626C7920697320636F6E74726F6C6C6564207468726F7567682074686520666F6C6C6F77696E670D0A2F2F20736574206F6620617474726962757465732E204368616E6765207468657365206174747269627574652076616C75657320746F206D6F646966792074686520696E666F726D6174696F6E0D0A2F2F206173736F636961746564207769746820616E20617373656D626C792E0D0A5B617373656D626C793A20417373656D626C795469746C652822456D61696C4C696222295D0D0A5B617373656D626C793A20417373656D626C794465736372697074696F6E282222295D0D0A5B617373656D626C793A20417373656D626C79436F6E66696775726174696F6E282222295D0D0A5B617373656D626C793A20417373656D626C79436F6D70616E7928224D656D6C696E6B20496E632E22295D0D0A5B617373656D626C793A20417373656D626C7950726F647563742822456D61696C4C696222295D0D0A5B617373656D626C793A20417373656D626C79436F707972696768742822436F7079726967687420C2A9204D656D6C696E6B20496E632E203230303722295D0D0A5B617373656D626C793A20417373656D626C7954726164656D61726B282222295D0D0A5B617373656D626C793A20417373656D626C7943756C74757265282222295D0D0A0D0A5B617373656D626C793A20436F6D56697369626C652866616C7365295D0D0A0D0A2F2F0D0A2F2F2056657273696F6E20696E666F726D6174696F6E20666F7220616E20617373656D626C7920636F6E7369737473206F662074686520666F6C6C6F77696E6720666F75722076616C7565733A0D0A2F2F0D0A2F2F2020202020204D616A6F722056657273696F6E0D0A2F2F2020202020204D696E6F722056657273696F6E0D0A2F2F2020202020204275696C64204E756D6265720D0A2F2F2020202020205265766973696F6E0D0A2F2F0D0A2F2F20596F752063616E207370656369667920616C6C207468652076616C756573206F7220796F752063616E2064656661756C7420746865205265766973696F6E20616E64204275696C64204E756D626572730D0A2F2F206279207573696E672074686520272A272061732073686F776E2062656C6F773A0D0A5B617373656D626C793A20417373656D626C7956657273696F6E2822312E302E2A22295D0D0A0D0A
AS N'Properties\AssemblyInfo.cs'

GO
EXEC sys.sp_addextendedproperty @.name=N'AutoDeployed', @.value=N'yes' , @.level0type=N'ASSEMBLY',@.level0name=N'EmailLib'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyProjectRoot', @.value=N'W:\ablelink\ableclr\EmailLib' , @.level0type=N'ASSEMBLY',@.level0name=N'EmailLib'

If your assembly is already deployed, adn you then copy the db over to some other server (detach, re-attach), then your assembly is there as well. The assemblies you are using from within SQL Server is not file based, but located in the database.

Niels
|||

No, I use backup and restore.

When I run my CLR functions, I get errors like

Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65886. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'datetimelib, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)

|||First of all, it looks like your assembly is in the restored database. Do: select * from sys.assemblies and see if yhe assembly is not there.

If it is, it looks like you have a permission problem, i.e. the database in question is not set up properly for the permission set the assembly requires. I assume the assembly is originally created with EXTERNAL_ACCESS or UNSAFE? If so, set up the datbase in the same way you did it originally.

Niels
|||I've been having a similar problem. Whenever I restore a database, where the restored database contains CLR stored procedures, I get the error you have (the stored procedures in the database worked fine though before being backedup/restored)

I've found this bug report which might be useful to you.
The workaround doesn't seem to solve my problem as I'm having the same issue even when it is on the same server:

http://support.microsoft.com/kb/918040

Looking at the output of:

Code Snippet

select * from sys.assemblies

select * from sys.assembly_files

everything looks identical before and after the backup/restore. The owner of the database is also the same as before the restore.

I'm not sure what's causing this, but I'd appreciate any tips from someone having similar problems.

How to add CLR dll after restore db on another server? thanks

I use VS2005 to deploy my CLR dll, it is easy. But after I copy db over to another server, the new server doesn't have driver W, so I need put dlls on Driver C. I think I have to relink assembly with those dlls. How should I do that, I tried Alter Assembly add file, but it doesn't work, the following is a script I get from one of my assembly. The dll is emaillib.dll, I could not find it in script. I have copied the dll to c:\emaillib.dll, so how can I link it back? I don;t want to drop the assembly since if I do that, I have to drop all CLR objects of the assembly first.

CREATE ASSEMBLY [EmailLib]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000......

WITH PERMISSION_SET = UNSAFE

GO
ALTER ASSEMBLY [EmailLib]
ADD FILE FROM 0x4D6963726F736F667420432F432B2B204D534620372E30300D0A1A445300000000020000020000000F00000048000000000000000D000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C0FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF38C0FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF.... bery long

AS N'EmailLib.pdb'

GO
ALTER ASSEMBLY [EmailLib]
ADD FILE FROM 0xEFBBBF7573696E672053797374656D2E5265666C656374696F6E3B0D0A7573696E672053797374656D2E52756E74696D652E436F6D70696C657253657276696365733B0D0A7573696E672053797374656D2E52756E74696D652E496E7465726F7053657276696365733B0D0A7573696E672053797374656D2E446174612E53716C3B0D0A0D0A2F2F2047656E6572616C20496E666F726D6174696F6E2061626F757420616E20617373656D626C7920697320636F6E74726F6C6C6564207468726F7567682074686520666F6C6C6F77696E670D0A2F2F20736574206F6620617474726962757465732E204368616E6765207468657365206174747269627574652076616C75657320746F206D6F646966792074686520696E666F726D6174696F6E0D0A2F2F206173736F636961746564207769746820616E20617373656D626C792E0D0A5B617373656D626C793A20417373656D626C795469746C652822456D61696C4C696222295D0D0A5B617373656D626C793A20417373656D626C794465736372697074696F6E282222295D0D0A5B617373656D626C793A20417373656D626C79436F6E66696775726174696F6E282222295D0D0A5B617373656D626C793A20417373656D626C79436F6D70616E7928224D656D6C696E6B20496E632E22295D0D0A5B617373656D626C793A20417373656D626C7950726F647563742822456D61696C4C696222295D0D0A5B617373656D626C793A20417373656D626C79436F707972696768742822436F7079726967687420C2A9204D656D6C696E6B20496E632E203230303722295D0D0A5B617373656D626C793A20417373656D626C7954726164656D61726B282222295D0D0A5B617373656D626C793A20417373656D626C7943756C74757265282222295D0D0A0D0A5B617373656D626C793A20436F6D56697369626C652866616C7365295D0D0A0D0A2F2F0D0A2F2F2056657273696F6E20696E666F726D6174696F6E20666F7220616E20617373656D626C7920636F6E7369737473206F662074686520666F6C6C6F77696E6720666F75722076616C7565733A0D0A2F2F0D0A2F2F2020202020204D616A6F722056657273696F6E0D0A2F2F2020202020204D696E6F722056657273696F6E0D0A2F2F2020202020204275696C64204E756D6265720D0A2F2F2020202020205265766973696F6E0D0A2F2F0D0A2F2F20596F752063616E207370656369667920616C6C207468652076616C756573206F7220796F752063616E2064656661756C7420746865205265766973696F6E20616E64204275696C64204E756D626572730D0A2F2F206279207573696E672074686520272A272061732073686F776E2062656C6F773A0D0A5B617373656D626C793A20417373656D626C7956657273696F6E2822312E302E2A22295D0D0A0D0A
AS N'Properties\AssemblyInfo.cs'

GO
EXEC sys.sp_addextendedproperty @.name=N'AutoDeployed', @.value=N'yes' , @.level0type=N'ASSEMBLY',@.level0name=N'EmailLib'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyProjectRoot', @.value=N'W:\ablelink\ableclr\EmailLib' , @.level0type=N'ASSEMBLY',@.level0name=N'EmailLib'

If your assembly is already deployed, adn you then copy the db over to some other server (detach, re-attach), then your assembly is there as well. The assemblies you are using from within SQL Server is not file based, but located in the database.

Niels
|||

No, I use backup and restore.

When I run my CLR functions, I get errors like

Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65886. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'datetimelib, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)

|||First of all, it looks like your assembly is in the restored database. Do: select * from sys.assemblies and see if yhe assembly is not there.

If it is, it looks like you have a permission problem, i.e. the database in question is not set up properly for the permission set the assembly requires. I assume the assembly is originally created with EXTERNAL_ACCESS or UNSAFE? If so, set up the datbase in the same way you did it originally.

Niels
|||I've been having a similar problem. Whenever I restore a database, where the restored database contains CLR stored procedures, I get the error you have (the stored procedures in the database worked fine though before being backedup/restored)

I've found this bug report which might be useful to you.
The workaround doesn't seem to solve my problem as I'm having the same issue even when it is on the same server:

http://support.microsoft.com/kb/918040

Looking at the output of:

Code Snippet

select * from sys.assemblies

select * from sys.assembly_files

everything looks identical before and after the backup/restore. The owner of the database is also the same as before the restore.

I'm not sure what's causing this, but I'd appreciate any tips from someone having similar problems.

How to access the SQL mobile database(.sdf) using ASP.Net?

How to access the SQL mobile database(.sdf) using ASP.Net?

When i try to add the System.Data.SqlServerCe.dll as a Reference it shows the following error.

Error 1 Could not load file or assembly 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

You must browse to this folder: C:\Programmer\Microsoft SQL Server Compact Edition\v3.1 and add the reference from there. In addition, you must follow the instructions in this article http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.aspx if you want to use the SqlCeConnection. Also note the advice on performance problems with SQL CE and asp.net. Please not there is a documentation error, see here for the correct syntax:

http://msdn2.microsoft.com/en-us/library/ms174612.aspx