Refreshing a User Defined Type

Hi,
Using a User Define Type (a class written in C# for use in SQL 2005).
I have added my assembly and created a new Type and tested inserting and
selecting.
I now need to add a new field to my class.
How can I update the assembly and the type without disrupting the data
that is currently stored under the UDT.
Many Thanks in Advance
Stuart
*** Sent via Developersdex http://www.examnotes.net ***Stuart Ferguson <stuart_ferguson1@.btinternet.com> wrote in
news:exAtpR7OGHA.3856@.TK2MSFTNGP12.phx.gbl:

> Using a User Define Type (a class written in C# for use in SQL 2005).
> I have added my assembly and created a new Type and tested inserting
> and selecting.
> I now need to add a new field to my class.
> How can I update the assembly and the type without disrupting the data
> that is currently stored under the UDT.
>
Unless you have created the UDT with Format.UserDefined you can't. If
you have created it with UserDefined you need to, in your Read method,
cater for the fact that the new field may not be available. Then you run
ALTER ASSEMBLY and you use the WITH UNCHECKED DATA option.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns9777AABB8FC2Bnielsbdevelopcom@.20
7.46.248.16...
> Unless you have created the UDT with Format.UserDefined you can't. If
> you have created it with UserDefined you need to, in your Read method,
> cater for the fact that the new field may not be available. Then you run
> ALTER ASSEMBLY and you use the WITH UNCHECKED DATA option.
Doing this is not a good idea for a number of reasons. It will only work if
your UDT is IsFixedLength = false. Any DBCC CheckTable command on your UDT
will now fail. If your UDT is IsByteOrdered=True, you will end up with 2
udts that have the same value but with different byte representation,
potentially returning wrong results in queries that filter on your UDT.
Additional reasons this is a bad idea are left unspecified.
A better approach is to make a new column and copy your UDT data into it in
either binary or string format. Drop the type and create it again from your
new assembly and reparse the old udt data into the new UDT column. This may
seem like a lot more work, but it's the only way to do it right.
Steven|||"Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
news:e$Y15v9OGHA.3164@.TK2MSFTNGP11.phx.gbl:

> Doing this is not a good idea for a number of reasons. It will only
> work if your UDT is IsFixedLength = false. Any DBCC CheckTable
> command on your UDT will now fail.
Well, I never said it was a good idea :-)

> If your UDT is IsByteOrdered=True,
> you will end up with 2 udts that have the same value but with
> different byte representation, potentially returning wrong results in
> queries that filter on your UDT.
Hmm, can you elaborate on that; if my read method caters for missing a
field, how can two udt's with the same value have different byte
representations?
[SNIP]
> A better approach is to make a new column and copy your UDT data into
> it in either binary or string format. Drop the type and create it
> again from your new assembly and reparse the old udt data into the new
> UDT column. This may seem like a lot more work, but it's the only way
> to do it right.
OK, so when you say re=parse the data, I assume you mean that the user
has to - before inserting the old data into the new column - either
update the old data so it contains some bogus value for the missing
field, or have the write method handle it?
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Thanks for the replies guys.
However how would i handle the case where a field was removed from the
UDT, would i then have to copy all the fields individually when copying
the class to another column ?
Stuart
*** Sent via Developersdex http://www.examnotes.net ***|||Stuart Ferguson <stuart_ferguson1@.btinternet.com> wrote in
news:u1v2IoGPGHA.740@.TK2MSFTNGP12.phx.gbl:

> However how would i handle the case where a field was removed from the
> UDT, would i then have to copy all the fields individually when
> copying the class to another column ?
>
Well, as I wrote in my original reply, the easiest way is that you
handle it in your read and write methods. However, if you copy the data
to a separate table, then - in this scenario - you should copy it as the
string representation. Then you would need to update that data and take
away the removed field. Finally you insert the data back into the table
with the updated type.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns97786A60D5CDAnielsbdevelopcom@.20
7.46.248.16...
> "Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
> news:e$Y15v9OGHA.3164@.TK2MSFTNGP11.phx.gbl:
> Hmm, can you elaborate on that; if my read method caters for missing a
> field, how can two udt's with the same value have different byte
> representations?
What if you run Alter Assembly and your read method doesn't get called
again? When queries refer to your IsByteOrdered udt, the udt isn't
instantiated so your read method isn't called.
--Assume Point is Format.UserDefined, IsByteOrdered=True,
IsFixedLength=False, and MaxByteSize leaves enough room for the alter
assembly expansion
CREATE TABLE DataPoints(c1 Point)
go
insert into DataPoints values(convert(Point, '(2,2)'))
go
--now alter the assembly that contains Point to add a new field to the type
ALTER ASSEMBLY Point_Assembly
go
--This won't return any rows because the bytes of the new udt Point for
(2,2) are different than the bytes for the old udt Point (2,2)
select * from DataPoints where c1 = convert(Point, '(2,2)')
In order for this to work, you'd need to write a stored-proc to go through
and 'refresh' your udt every place it is persisted. If you're going to do
this, you might as well do it the safe way and drop the type to ensure that
you catch all cases and don't hit any surprises.

> OK, so when you say re=parse the data, I assume you mean that the user
> has to - before inserting the old data into the new column - either
> update the old data so it contains some bogus value for the missing
> field, or have the write method handle it?
I think you mean the Read method, but that's correct. If the String
representation of the type doesn't change, then the user could go through
the Parse method instead without requiring additional work.
Obviously, changing the serialization of your UDT when it's already in use
is something to be avoided if at all possible.
Steven|||"Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
news:OEBRH7IPGHA.2440@.TK2MSFTNGP11.phx.gbl:

> What if you run Alter Assembly and your read method doesn't get called
> again? When queries refer to your IsByteOrdered udt, the udt isn't
> instantiated so your read method isn't called.
> --Assume Point is Format.UserDefined, IsByteOrdered=True,
> IsFixedLength=False, and MaxByteSize leaves enough room for the alter
> assembly expansion
> CREATE TABLE DataPoints(c1 Point)
> go
> insert into DataPoints values(convert(Point, '(2,2)'))
> go
> --now alter the assembly that contains Point to add a new field to the
> type ALTER ASSEMBLY Point_Assembly
> go
> --This won't return any rows because the bytes of the new udt Point
> for (2,2) are different than the bytes for the old udt Point (2,2)
> select * from DataPoints where c1 = convert(Point, '(2,2)')
>
Hmm, I see your point (pun intended).
Quick question, if this is now the case, is there any reason for ALTER
ASSEMBLY ... WITH UNCHECKED DATA? I.e., when changing a type we should
always move the data to some other table and then re-populate.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns977952A6CDE45nielsbdevelopcom@.20
7.46.248.16...
> Quick question, if this is now the case, is there any reason for ALTER
> ASSEMBLY ... WITH UNCHECKED DATA? I.e., when changing a type we should
> always move the data to some other table and then re-populate.
Yep - Alter Assembly's primary use case is for fixing bugs in your code.
With UDTs, you may have a bug in one of the methods that you want to fix or
you may need to add a new method. With Format.Native UDTs, you can do this
without requiring WITH UNCHECKED DATA even when your type is persisted
because the server can verify that the serialization did not change.
(UNCHECKED DATA might still be required in cases where a UDT method is
persisted somewhere). With Format.UserDefined UDTs, you could change the
serialization and the server would never know it so WITH UNCHECKED DATA is
required even if you are not modifying the serialization.
Steven

Referencing SQLDMO in UNSAFE assembly

I created a SQL Server Project in VS 2005, and tried to add a reference to SQLDMO Object dll. But the reference --> add reference, does not allow it. It does not work, even if I set the Assembly Permission Level to UNAFE/EXTERNAL ACCESS.

Reason I'm trying to do this:

We have a SQL 2000 stored procedure that uses SQLDMO using sp_OACreate to BCP files to database. We are converting to 2005 and because of SOX restrictions, I'm trying to replace the sp_OACreate part with external stored procedure written in C#.

According to BOL, I thought atleast UNSAFE should support this, but it seems not.

Probably, I'm expecting too much..

Your help is greatly appreciated.

Thanks

Thanks to Vineet Rao's posting to one of of the answers on this forum, to a similar problem.

I registered "signed" InterOp.SQLDMO.dll (which is obtained by referecing to a regular project) , as UNSAFE, and it appeared in the Reference section of SQL Server Project.

Thanks

Baskar

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

Reference to a non-shared member requires an object reference

Hi,
I tried to use a custom assembly, written in C#. I received the following
error:
"[BC30469] Reference to a non-shared member requires an object reference..."
Here's the detail info for the custom assembly:
*I have 2 C# files. I did not write the code. So my C# knowledge is
limited. I removed some of the code within the methods because it gets very
long. Here is the code:
--[IConvertor.cs]--
using System;
namespace sbu.convertor
{
public interface IConvertor
{
string convert(string label, string direction, string val);
string convertByUnit(string before, string after, string val, string
direction, string group, string format);
string convertByUnit2(string before, string after, string val, string
direction);
string getUnit(string usUnit, string saUnit, string direction, string
group);
string getUnit2(string usUnit, string saUnit, string direction);
}
}
--[Convertor.cs]--
using System;
using System.IO;
using System.Xml;
using System.Globalization;
namespace sbu.convertor
{
public class Convertor:IConvertor
{
private static Convertor instance;
public static IConvertor getInstance()
{
if (instance==null)
{
instance = new Convertor();
}
return instance;
}
private XmlDocument doc;
private string docSource = null;
private NumberFormatInfo format;
public Convertor()
{
if(File.Exists("C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml"))
{
//pdu dev box
docSource = "C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml";
}
if(File.Exists("C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml"))
{
//webdevtest
docSource = "C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml";
}
doc = new XmlDocument();
doc.Load(docSource);
format = new NumberFormatInfo();
format.NumberDecimalDigits = 3;
format.NumberGroupSizes = new int[] {3};
format.NumberGroupSeparator = ",";
format.NumberDecimalSeparator = ".";
}
public string convert(string label, string direction, string val)
{
return (double.Parse(val)*getRatio(label,direction)).ToString();
//return "0000000000";
}
private double getRatio(string label, string direction)
{
XmlNode node = doc.SelectSingleNode("RICETEC/LABEL/CONVERSION[@.ID='"+label+"']/"+direction);
string valStr = node.InnerText;
return double.Parse(valStr);
}
public string convertByUnit(string usUnit, string saUnit, string val,
string direction, string group, string format)
{
////
}
public string convertByUnit2(string before, string after, string val,
string direction)
{
////
}
public string getUnit(string usUnit, string saUnit, string direction,
string group)
{
///
}
public string getUnit2(string usUnit, string saUnit, string direction)
{
///
}
public string convertTemp(string val, string direction)
{
///
}
}
}
* Here is how I complied it:
csc /t:library /out:bin/sbu_convertor.dll *.cs
Thanks in advance. JLI found a solution. I simply added the class and instance names in the
Reference tab. That fixed it.
"JL" wrote:
> Hi,
> I tried to use a custom assembly, written in C#. I received the following
> error:
> "[BC30469] Reference to a non-shared member requires an object reference..."
>
> Here's the detail info for the custom assembly:
> *I have 2 C# files. I did not write the code. So my C# knowledge is
> limited. I removed some of the code within the methods because it gets very
> long. Here is the code:
> --[IConvertor.cs]--
> using System;
> namespace sbu.convertor
> {
> public interface IConvertor
> {
> string convert(string label, string direction, string val);
> string convertByUnit(string before, string after, string val, string
> direction, string group, string format);
> string convertByUnit2(string before, string after, string val, string
> direction);
> string getUnit(string usUnit, string saUnit, string direction, string
> group);
> string getUnit2(string usUnit, string saUnit, string direction);
> }
> }
>
> --[Convertor.cs]--
> using System;
> using System.IO;
> using System.Xml;
> using System.Globalization;
> namespace sbu.convertor
> {
> public class Convertor:IConvertor
> {
> private static Convertor instance;
> public static IConvertor getInstance()
> {
> if (instance==null)
> {
> instance = new Convertor();
> }
> return instance;
> }
> private XmlDocument doc;
> private string docSource = null;
> private NumberFormatInfo format;
> public Convertor()
> {
> if(File.Exists("C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml"))
> {
> //pdu dev box
> docSource => "C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml";
> }
> if(File.Exists("C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml"))
> {
> //webdevtest
> docSource => "C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml";
> }
> doc = new XmlDocument();
> doc.Load(docSource);
> format = new NumberFormatInfo();
> format.NumberDecimalDigits = 3;
> format.NumberGroupSizes = new int[] {3};
> format.NumberGroupSeparator = ",";
> format.NumberDecimalSeparator = ".";
> }
> public string convert(string label, string direction, string val)
> {
> return (double.Parse(val)*getRatio(label,direction)).ToString();
> //return "0000000000";
> }
> private double getRatio(string label, string direction)
> {
> XmlNode node => doc.SelectSingleNode("RICETEC/LABEL/CONVERSION[@.ID='"+label+"']/"+direction);
> string valStr = node.InnerText;
> return double.Parse(valStr);
> }
> public string convertByUnit(string usUnit, string saUnit, string val,
> string direction, string group, string format)
> {
> ////
> }
> public string convertByUnit2(string before, string after, string val,
> string direction)
> {
> ////
> }
> public string getUnit(string usUnit, string saUnit, string direction,
> string group)
> {
>
> ///
> }
> public string getUnit2(string usUnit, string saUnit, string direction)
> {
> ///
> }
> public string convertTemp(string val, string direction)
> {
> ///
> }
> }
> }
>
> * Here is how I complied it:
> csc /t:library /out:bin/sbu_convertor.dll *.cs
>
> Thanks in advance. JL

Reference report items within a custom assembly?

How do I reference report items (such as textboxes, datasets, tables, etc...) from within a custom assembly?

Thanks!

You have pass them as paremeters (reference or by value) to the custom assembly method.

Shyam