refreshview removes user defined functions from sysdepends

When I create a procedure that references a user defined procedure it
appears in sysdepends, but disapears after being refreshed. Is this
expected and is there an alternative to sp_refreshview that correctly
refreshes sysdepends
--0--0--
print 'drop depenencies'
go
drop view depends_test
go
print 'create view'
go
create view depends_test as
select
dbo.ufJsmTranslate('test') As test_column
from
(select 1 one) test
go
print 'check depenencies'
go
sp_depends depends_test
go
print 'refresh'
go
sp_refreshview depends_test
go
print 'check depenencies'
go
sp_depends depends_test
--0--0--
drop depenencies
create view
check depenencies
In the current database, the specified object references the following:
name type updated selected column
-- -- -- -- --
dbo.ufJsmTranslate scalar function no no
refresh
check depenencies
Object does not reference any object, and no objects reference it.
--0--0--May be this is a bug, It seems that sp_refreshview does not update reference
s
to udfs. Here I have a script with the same problem. I also tried to create
the udf and view with the SCHEMABINDING property, everything was ok, except
that sp_refresh is giving me an error when I try to refresh the view.
-- same problem
use northwind
go
create table t(colA int)
go
create function dbo.ufn_funct1 (
@.i int
)
returns int
as
begin
return (@.i)
end
go
create view myview
as
select dbo.ufn_funct1(colA) as colA from t
go
exec sp_depends myview
go
exec sp_refreshview myview
go
exec sp_depends myview
go
drop view myview
go
drop function dbo.ufn_funct1
go
drop table t
go
-- Tried to fix it using schemabinfing
-- sp_refreshview is giving me an error
use northwind
go
create table t(colA int)
go
create function dbo.ufn_funct1 (
@.i int
)
returns int
with schemabinding
as
begin
return (@.i)
end
go
create view dbo.myview
with schemabinding
as
select dbo.ufn_funct1(colA) as colA from dbo.t
go
exec sp_depends 'dbo.myview'
go
exec sp_refreshview 'dbo.myview'
go
-- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
-- Invalid object name 'dbo.myview'.
exec sp_depends 'dbo.myview'
go
drop view dbo.myview
go
drop function dbo.ufn_funct1
go
drop table t
go
AMB
"bilbo.baggins@.freesurf.ch" wrote:

> When I create a procedure that references a user defined procedure it
> appears in sysdepends, but disapears after being refreshed. Is this
> expected and is there an alternative to sp_refreshview that correctly
> refreshes sysdepends
> --0--0--
> print 'drop depenencies'
> go
> drop view depends_test
> go
> print 'create view'
> go
> create view depends_test as
> select
> dbo.ufJsmTranslate('test') As test_column
> from
> (select 1 one) test
> go
> print 'check depenencies'
> go
> sp_depends depends_test
> go
> print 'refresh'
> go
> sp_refreshview depends_test
> go
> print 'check depenencies'
> go
> sp_depends depends_test
> --0--0--
> drop depenencies
> create view
> check depenencies
> In the current database, the specified object references the following:
> name type updated selected column
> -- -- -- -- --
> dbo.ufJsmTranslate scalar function no no
> refresh
> check depenencies
> Object does not reference any object, and no objects reference it.
> --0--0--
>|||Correction,

> May be this is a bug, It seems that sp_refreshview does not update referen
ces
> to udfs. Here I have a script with the same problem. I also tried to creat
e
> the udf and view with the SCHEMABINDING property, everything was ok, excep
t
> that sp_refresh is giving me an error when I try to refresh the view.
..., except that sp_refreshview ...

> -- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
> -- Invalid object name 'dbo.myview'.
> exec sp_depends 'dbo.myview'
> go
-- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
-- Invalid object name 'dbo.myview'.
exec sp_refreshview 'dbo.myview'
go
AMB
"Alejandro Mesa" wrote:
> May be this is a bug, It seems that sp_refreshview does not update referen
ces
> to udfs. Here I have a script with the same problem. I also tried to creat
e
> the udf and view with the SCHEMABINDING property, everything was ok, excep
t
> that sp_refresh is giving me an error when I try to refresh the view.
> -- same problem
> use northwind
> go
> create table t(colA int)
> go
> create function dbo.ufn_funct1 (
> @.i int
> )
> returns int
> as
> begin
> return (@.i)
> end
> go
> create view myview
> as
> select dbo.ufn_funct1(colA) as colA from t
> go
> exec sp_depends myview
> go
> exec sp_refreshview myview
> go
> exec sp_depends myview
> go
> drop view myview
> go
> drop function dbo.ufn_funct1
> go
> drop table t
> go
> -- Tried to fix it using schemabinfing
> -- sp_refreshview is giving me an error
> use northwind
> go
> create table t(colA int)
> go
> create function dbo.ufn_funct1 (
> @.i int
> )
> returns int
> with schemabinding
> as
> begin
> return (@.i)
> end
> go
> create view dbo.myview
> with schemabinding
> as
> select dbo.ufn_funct1(colA) as colA from dbo.t
> go
> exec sp_depends 'dbo.myview'
> go
> exec sp_refreshview 'dbo.myview'
> go
> -- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
> -- Invalid object name 'dbo.myview'.
> exec sp_depends 'dbo.myview'
> go
> drop view dbo.myview
> go
> drop function dbo.ufn_funct1
> go
> drop table t
> go
>
> AMB
>
> "bilbo.baggins@.freesurf.ch" wrote:
>

Referring to Sql connection string in web.config from code behind

Every time I move my project from my computer to the testing server I have to change the connection string references in the aspx side and in my code behind.

For the code behind I declared the SqlConnection string at the top of the code-behind page for both connection strings and comment out the one not in use. Obviously I then comment out the string not in use in the WebConfig as well.

Being superlatively lazy I always look for the easiest and quickest way to do most anything - connection strings included. Having to comment out strings twice brought rise to the question of whether I can refer to the connection string in the web.config file from the code-behind page. I'm sure it can be done, and I did a good amount of hunting around, but I couldn't find any examples of code that would do that.

Currently, as I said above, I have two connection strings declared at the top of my code-behind. Here's an example of one:

Private sqlConnAs New SqlConnection("Data Source=DATABASESERVER;Initial Catalog=DATABASE;User ID=USER;Password=PASSWORD")

Then, I just use sqlConn as usual in my binding without having to "dim" it in every sub:

sdaPersonnel =New SqlDataAdapter(strSqlPersonnel, sqlConn)

Then there's the SqlConnections set up by the wizard on the aspx side:

<asp:SqlDataSource ID="sqlDataSourcePayrollCompany" Runat="Server" ConnectionString="<%$ ConnectionStrings:DATABASECONNECTIONSTRING%>" ...>

And for the connection in the web.config:

<add name="DATABASECONNECTIONSTRING" connectionString="Data Source=DATABASESERVER;Initial Catalog=DATABASE;User ID=USER;Password=PASSWORD" providerName="System.Data.SqlClient" />

So, what would be the code in the code-behind page to refer to the connection string in the web.config file?

Thanks!

To refer to the connection string via code: ConfigurationManager.ConnectionStrings["<ConnectionStringName>"]|||

Hey, Stiletto, Thanks! That did the trick (or, at least, it got me on the right track). I wasn't exaclty sure how to actually add that to my connection string code, so I had to do some hunting and trying different syntax, but ended up with this working:

Private sqlConnAs New SqlConnection(ConfigurationManager.ConnectionStrings("DATABASECONNECTIONSTRING").ConnectionString)

Thanks again!

Referencing xs:schema within a SCHEMA COLLECTION

Hi all,
I have an XML schema that has an element declaration that references a W3C
XML Schema type:
<xs:import namespace="http://www.w3.org/2001/XMLSchema"/>
. . .
<xs:element ref="xs:schema"/>
When I try and import this schema into a SQL SCHEMA COLLECTION, I get an
error:
Msg 2308, Level 16, State 1, Line 1
Reference to an undefined name 'schema' within namespace
'http://www.w3.org/2001/XMLSchema'
The problem is obviously that the schema processor does not have a copy of
the W3C XML Schema, uh, schema handy. I tried getting a copy of the XML
Schema reference from the W3C and cleaning up the documentation nodes
(riddled with single quotes). The schema processor then gave me an "invalid
target namespace specified error.
Any ideas for having a node that contains a schema? How would the schema
for WSDL be accepted? Thanks,
- ErikHi Eric,
You don't need to import the schema for schemas (apparently it even causes
problems) because its built-in to SQL Server 2005. Just refer to it in your
schema. Like this:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" ...more >
...
<xs:element name="yourtype" type="xs:string" />
</xs:schema>
Deriving types would work the same way.
Weird error message, I'll admit.
Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:E4DBFFC7-28FF-4C89-BD0A-F31502BA3E36@.microsoft.com...
> Hi all,
> I have an XML schema that has an element declaration that references a W3C
> XML Schema type:
> <xs:import namespace="http://www.w3.org/2001/XMLSchema"/>
> . . .
> <xs:element ref="xs:schema"/>
> When I try and import this schema into a SQL SCHEMA COLLECTION, I get an
> error:
> Msg 2308, Level 16, State 1, Line 1
> Reference to an undefined name 'schema' within namespace
> 'http://www.w3.org/2001/XMLSchema'
> The problem is obviously that the schema processor does not have a copy of
> the W3C XML Schema, uh, schema handy. I tried getting a copy of the XML
> Schema reference from the W3C and cleaning up the documentation nodes
> (riddled with single quotes). The schema processor then gave me an
> "invalid
> target namespace specified error.
> Any ideas for having a node that contains a schema? How would the schema
> for WSDL be accepted? Thanks,
> - Erik
>|||Thanks Bob.
If I declare the schema type as xs:string, I'll have to escape XML markup in
the embedded schema node. The work-around I picked instead was to declare a
n
xs:any rather than an xs:element. I can tell the schema process to skip
processing on the embedded node that way.
But I really wanted to have SQL Server validate my documents fully,
including the embedded schemas. I'll think about that over the wend and
let everyone know what I come up with.
Cheers,
Erik Johnson
http://appside.blogspot.com
"Bob Beauchemin" wrote:

> Hi Eric,
> You don't need to import the schema for schemas (apparently it even causes
> problems) because its built-in to SQL Server 2005. Just refer to it in you
r
> schema. Like this:
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" ...more >
> ...
> <xs:element name="yourtype" type="xs:string" />
> </xs:schema>
> Deriving types would work the same way.
> Weird error message, I'll admit.
> Hope this helps,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
> news:E4DBFFC7-28FF-4C89-BD0A-F31502BA3E36@.microsoft.com...
>
>|||Hi Erik, could you please send me email to describe what you would like to
do? We then can take a look at why it is not working.
Note that the Schema for the W3C Schema is not a valid schema in its own.
Best regards
Michael
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:228DE552-E347-4906-A1E0-F321E00FF3BC@.microsoft.com...
> Thanks Bob.
> If I declare the schema type as xs:string, I'll have to escape XML markup
> in
> the embedded schema node. The work-around I picked instead was to declare
> an
> xs:any rather than an xs:element. I can tell the schema process to skip
> processing on the embedded node that way.
> But I really wanted to have SQL Server validate my documents fully,
> including the embedded schemas. I'll think about that over the wend
> and
> let everyone know what I come up with.
> Cheers,
> Erik Johnson
> http://appside.blogspot.com
> "Bob Beauchemin" wrote:
>|||Hi Michael,
Thanks for looking into this. We are creating some schemas for XML
documents that contain XML schemas. Here is a simple example:
CREATE XML SCHEMA COLLECTION TestSchemas AS
'<xs:schema id="Noun"
targetNamespace="http://tempuri.org/Noun.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/Noun.xsd"
xmlns:mstns="http://tempuri.org/Noun.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:import namespace="http://www.w3.org/2001/XMLSchema" />
<xs:element name="Noun">
<xs:complexType>
<xs:sequence>
<xs:element ref="xs:schema" />
</xs:sequence>
<xs:attribute name="uri" use="required">
<xs:simpleType>
<xs:restriction base="xs:anyURI"/>
</xs:simpleType>
</xs:attribute>
</xs:complexType>
</xs:element>
</xs:schema>'
I get this message:
Msg 2308, Level 16, State 1, Line 1
Reference to an undefined name 'schema' within namespace
'http://www.w3.org/2001/XMLSchema'
And you are right. The sample XSD on the W3C website is not usable,
although I tried to shoehorn it! Thanks again,
- Erik
"Michael Rys [MSFT]" wrote:

> Hi Erik, could you please send me email to describe what you would like to
> do? We then can take a look at why it is not working.
> Note that the Schema for the W3C Schema is not a valid schema in its own.
> Best regards
> Michael
> "Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
> news:228DE552-E347-4906-A1E0-F321E00FF3BC@.microsoft.com...
>
>|||I see. Even if you remove the import, this fails. Also means that I can't
catalog the schemas in the WSDL file put out by SQL Server 2005 XML Web
Services feature. For example,
http://schemas.microsoft.com/sqlserver/2004/SOAP/types. Even if I change the
target namespace. Hmmm...
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:EDC61844-1E9B-4B71-8B15-4686FAAB8A5F@.microsoft.com...
> Hi Michael,
> Thanks for looking into this. We are creating some schemas for XML
> documents that contain XML schemas. Here is a simple example:
> CREATE XML SCHEMA COLLECTION TestSchemas AS
> '<xs:schema id="Noun"
> targetNamespace="http://tempuri.org/Noun.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/Noun.xsd"
> xmlns:mstns="http://tempuri.org/Noun.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:import namespace="http://www.w3.org/2001/XMLSchema" />
> <xs:element name="Noun">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="xs:schema" />
> </xs:sequence>
> <xs:attribute name="uri" use="required">
> <xs:simpleType>
> <xs:restriction base="xs:anyURI"/>
> </xs:simpleType>
> </xs:attribute>
> </xs:complexType>
> </xs:element>
> </xs:schema>'
> I get this message:
> Msg 2308, Level 16, State 1, Line 1
> Reference to an undefined name 'schema' within namespace
> 'http://www.w3.org/2001/XMLSchema'
> And you are right. The sample XSD on the W3C website is not usable,
> although I tried to shoehorn it! Thanks again,
> - Erik
>
> "Michael Rys [MSFT]" wrote:
>

Referencing xs:schema within a SCHEMA COLLECTION

Hi all,
I have an XML schema that has an element declaration that references a W3C
XML Schema type:
<xs:import namespace="http://www.w3.org/2001/XMLSchema"/>
. . .
<xs:element ref="xs:schema"/>
When I try and import this schema into a SQL SCHEMA COLLECTION, I get an
error:
Msg 2308, Level 16, State 1, Line 1
Reference to an undefined name 'schema' within namespace
'http://www.w3.org/2001/XMLSchema'
The problem is obviously that the schema processor does not have a copy of
the W3C XML Schema, uh, schema handy. I tried getting a copy of the XML
Schema reference from the W3C and cleaning up the documentation nodes
(riddled with single quotes). The schema processor then gave me an "invalid
target namespace specified error.
Any ideas for having a node that contains a schema? How would the schema
for WSDL be accepted? Thanks,
- Erik
Hi Eric,
You don't need to import the schema for schemas (apparently it even causes
problems) because its built-in to SQL Server 2005. Just refer to it in your
schema. Like this:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" ...more >
...
<xs:element name="yourtype" type="xs:string" />
</xs:schema>
Deriving types would work the same way.
Weird error message, I'll admit.
Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:E4DBFFC7-28FF-4C89-BD0A-F31502BA3E36@.microsoft.com...
> Hi all,
> I have an XML schema that has an element declaration that references a W3C
> XML Schema type:
> <xs:import namespace="http://www.w3.org/2001/XMLSchema"/>
> . . .
> <xs:element ref="xs:schema"/>
> When I try and import this schema into a SQL SCHEMA COLLECTION, I get an
> error:
> Msg 2308, Level 16, State 1, Line 1
> Reference to an undefined name 'schema' within namespace
> 'http://www.w3.org/2001/XMLSchema'
> The problem is obviously that the schema processor does not have a copy of
> the W3C XML Schema, uh, schema handy. I tried getting a copy of the XML
> Schema reference from the W3C and cleaning up the documentation nodes
> (riddled with single quotes). The schema processor then gave me an
> "invalid
> target namespace specified error.
> Any ideas for having a node that contains a schema? How would the schema
> for WSDL be accepted? Thanks,
> - Erik
>
|||Thanks Bob.
If I declare the schema type as xs:string, I'll have to escape XML markup in
the embedded schema node. The work-around I picked instead was to declare an
xs:any rather than an xs:element. I can tell the schema process to skip
processing on the embedded node that way.
But I really wanted to have SQL Server validate my documents fully,
including the embedded schemas. I'll think about that over the weekend and
let everyone know what I come up with.
Cheers,
Erik Johnson
http://appside.blogspot.com
"Bob Beauchemin" wrote:

> Hi Eric,
> You don't need to import the schema for schemas (apparently it even causes
> problems) because its built-in to SQL Server 2005. Just refer to it in your
> schema. Like this:
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" ...more >
> ...
> <xs:element name="yourtype" type="xs:string" />
> </xs:schema>
> Deriving types would work the same way.
> Weird error message, I'll admit.
> Hope this helps,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
> news:E4DBFFC7-28FF-4C89-BD0A-F31502BA3E36@.microsoft.com...
>
>
|||Hi Erik, could you please send me email to describe what you would like to
do? We then can take a look at why it is not working.
Note that the Schema for the W3C Schema is not a valid schema in its own.
Best regards
Michael
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:228DE552-E347-4906-A1E0-F321E00FF3BC@.microsoft.com...[vbcol=seagreen]
> Thanks Bob.
> If I declare the schema type as xs:string, I'll have to escape XML markup
> in
> the embedded schema node. The work-around I picked instead was to declare
> an
> xs:any rather than an xs:element. I can tell the schema process to skip
> processing on the embedded node that way.
> But I really wanted to have SQL Server validate my documents fully,
> including the embedded schemas. I'll think about that over the weekend
> and
> let everyone know what I come up with.
> Cheers,
> Erik Johnson
> http://appside.blogspot.com
> "Bob Beauchemin" wrote:
|||Hi Michael,
Thanks for looking into this. We are creating some schemas for XML
documents that contain XML schemas. Here is a simple example:
CREATE XML SCHEMA COLLECTION TestSchemas AS
'<xs:schema id="Noun"
targetNamespace="http://tempuri.org/Noun.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/Noun.xsd"
xmlns:mstns="http://tempuri.org/Noun.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:import namespace="http://www.w3.org/2001/XMLSchema" />
<xs:element name="Noun">
<xs:complexType>
<xs:sequence>
<xs:element ref="xs:schema" />
</xs:sequence>
<xs:attribute name="uri" use="required">
<xs:simpleType>
<xs:restriction base="xs:anyURI"/>
</xs:simpleType>
</xs:attribute>
</xs:complexType>
</xs:element>
</xs:schema>'
I get this message:
Msg 2308, Level 16, State 1, Line 1
Reference to an undefined name 'schema' within namespace
'http://www.w3.org/2001/XMLSchema'
And you are right. The sample XSD on the W3C website is not usable,
although I tried to shoehorn it! Thanks again,
- Erik
"Michael Rys [MSFT]" wrote:

> Hi Erik, could you please send me email to describe what you would like to
> do? We then can take a look at why it is not working.
> Note that the Schema for the W3C Schema is not a valid schema in its own.
> Best regards
> Michael
> "Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
> news:228DE552-E347-4906-A1E0-F321E00FF3BC@.microsoft.com...
>
>
|||I see. Even if you remove the import, this fails. Also means that I can't
catalog the schemas in the WSDL file put out by SQL Server 2005 XML Web
Services feature. For example,
http://schemas.microsoft.com/sqlserver/2004/SOAP/types. Even if I change the
target namespace. Hmmm...
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:EDC61844-1E9B-4B71-8B15-4686FAAB8A5F@.microsoft.com...[vbcol=seagreen]
> Hi Michael,
> Thanks for looking into this. We are creating some schemas for XML
> documents that contain XML schemas. Here is a simple example:
> CREATE XML SCHEMA COLLECTION TestSchemas AS
> '<xs:schema id="Noun"
> targetNamespace="http://tempuri.org/Noun.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/Noun.xsd"
> xmlns:mstns="http://tempuri.org/Noun.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:import namespace="http://www.w3.org/2001/XMLSchema" />
> <xs:element name="Noun">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="xs:schema" />
> </xs:sequence>
> <xs:attribute name="uri" use="required">
> <xs:simpleType>
> <xs:restriction base="xs:anyURI"/>
> </xs:simpleType>
> </xs:attribute>
> </xs:complexType>
> </xs:element>
> </xs:schema>'
> I get this message:
> Msg 2308, Level 16, State 1, Line 1
> Reference to an undefined name 'schema' within namespace
> 'http://www.w3.org/2001/XMLSchema'
> And you are right. The sample XSD on the W3C website is not usable,
> although I tried to shoehorn it! Thanks again,
> - Erik
>
> "Michael Rys [MSFT]" wrote:

References other assembly with error...

I have a vb.net project which compiled to CommonLibrary.dll with no error
when I add references CommonLibrary.dll on my reporting project,
it will show an error(as following) when I compile the reporting project
Error message:
"\TestReport.rdl Error while loading code module: â'CommonLibrary,
Version=1.0.1825.16800, Culture=neutral, PublicKeyToken=nullâ'. Details: ?"
Am I missing something or do something wrong'
Thx, pls helptry adding the dll again to the report by going to report properties and
selecting the dll again. this could be because the report is referencing an
old version of the dll.|||See URL: http://blogs.sqlxml.org/bryantlikes/articles/824.aspx
(thanks Bryant)
It is said that you must copy compiled dll to the folder:
C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer
"Louie" wrote:
> I have a vb.net project which compiled to CommonLibrary.dll with no error
> when I add references CommonLibrary.dll on my reporting project,
> it will show an error(as following) when I compile the reporting project
> Error message:
> "\TestReport.rdl Error while loading code module: â'CommonLibrary,
> Version=1.0.1825.16800, Culture=neutral, PublicKeyToken=nullâ'. Details: ?"
> Am I missing something or do something wrong'
> Thx, pls help

References in SQL Server Projects

Hi,

I'm using Visual Studio Beta 1 with SQL Server 2005 Beta 2 and have created a SQL Server Project.

The first thing I tried was to add a reference to a C# class library project that contains many C# tools I like.

Bad news, I get the following error message:

"A reference to 'csharplibrary' could not be added. SQL Server projects can reference only other SQL Server projects."

My question is: is this behavior a limitation of any of the Betas, or is it going to stay? This greatly limits the use of the CLR in SQL Server, IMO.

Martin

Hi,

There are various different approaches you can follow to get your scenario working.
1. You can pre-register all the assemblies that you want to refer in your project - inside your SQL Server database using the CREATE ASSEMBLY statement. Once you are done with that, you can refer them in Visual Studio SQL Server projects using the add reference (they should be shown in the list).
2. You can use class library projects (that allows you to reference any assemblies on the disk) to build your assembly and then register them via SQL Server Management Studio using the CREATE ASSEMBLY statement.

The behavior that you are seeing is going to stay as-is in Visual Studio 2005.

Thanks,
-Vineet.|||
Vineet, thanks for that precious information. It's working... almost.

The assembly is registered, but I get the following error when trying to reference it in Visual Studio for the SQL Server project:
A reference to 'classlibrary' could not be added. This is not a valid assembly or COM component. Only assemblies with extension 'dll' or 'manifest' or COM components can be referenced. Please make sure that the file is accessible, and that it is a valid assembly or COM component.
I've used the same assembly in other VS projects and it's working fine. It's a very basic C# class library/dll with one class containing one static method (no static field).

Any suggestions?

Martin|||

Hi Martin,

This is a known bug and has been fixed in Visual Studio 2005 feb CTP onwards.

Thanks,
-Vineet.