Filter data back to publisher

Hi all,

We have merge replication set up between 2 instances of SQL 2005 through web synchronization. I was wondering is it possible if the subscriber adds data at their end can we selectively edit what data gets uploaded back to the publisher? Will this work if I add a filter to the appropriate article at the publisher or will that only limit what goes down to the subscriber rather than vice-versa?

Also, could we set up web sychronization if the publisher had no direct access to the IIS server (ie. was not on the same network, available only through port 80, 443)?

Thanks,

Iain

Hey lain,

You can't selectively edit what data gets uploaded back to the publisher. There's a parameter that you can use in sp_addmergearticle @.subscriber_upload_options which you can set to (0,1,2) NO restrictions, changes are allowed at sub but not uploaded to the publisher , and last option doesn't allow any changed at the subscriber respectively. So you can either upload all the changes or none of the changes, but you can't filter out the data to be uploaded.

There's a workaround but that's not recommended. You can enable the insert triggers on the data you want to upload to the publisher, and then disable the insert triggers on the data you want to filter out. By doing that merge agent won’t detect those changes and they won't be uploaded to the publisher.

hope that helps

bishoyy

|||

Ok, thanks. Looks like if they want to implement their requirement they will just have to become the publisher. I think messing the the triggers will just lead to all sorts of issues and problems that I could well do without!

Thanks for the help.

Filter data back to publisher

Hi all,

We have merge replication set up between 2 instances of SQL 2005 through web synchronization. I was wondering is it possible if the subscriber adds data at their end can we selectively edit what data gets uploaded back to the publisher? Will this work if I add a filter to the appropriate article at the publisher or will that only limit what goes down to the subscriber rather than vice-versa?

Also, could we set up web sychronization if the publisher had no direct access to the IIS server (ie. was not on the same network, available only through port 80, 443)?

Thanks,

Iain

Hey lain,

You can't selectively edit what data gets uploaded back to the publisher. There's a parameter that you can use in sp_addmergearticle @.subscriber_upload_options which you can set to (0,1,2) NO restrictions, changes are allowed at sub but not uploaded to the publisher , and last option doesn't allow any changed at the subscriber respectively. So you can either upload all the changes or none of the changes, but you can't filter out the data to be uploaded.

There's a workaround but that's not recommended. You can enable the insert triggers on the data you want to upload to the publisher, and then disable the insert triggers on the data you want to filter out. By doing that merge agent won’t detect those changes and they won't be uploaded to the publisher.

hope that helps

bishoyy

|||

Ok, thanks. Looks like if they want to implement their requirement they will just have to become the publisher. I think messing the the triggers will just lead to all sorts of issues and problems that I could well do without!

Thanks for the help.

FileUpload

I have gotten FileUpload in VB.Net to work fine in saving a file to a web server folder but cannot find some examples on how to save the file as an image on sql200. Can anyone point me in the write direction - thanks.

Please refer to this article, which contains a sample about how to deal with BLOB data in SQL Server:
http://forums.asp.net/thread/1254026.aspx

|||

i dont like it, but i figured it out and have written about in my blog

http://weblogs.asp.net/hpreishuber/archive/2006/02/17/438498.aspx

|||

string fileName = FileUpload1.PostedFile.FileName;

byte[] lc_ByteFile = new byte[FileUpload1.FileContent.Length];

lc_ByteFile = ReadFileToByteArray(fileName, FileUpload1.FileContent.Length);

if (FileUpload1.FileName.EndsWith(".jpg") || FileUpload1.FileName.EndsWith(".jpeg") || FileUpload1.FileName.EndsWith(".gif") || FileUpload1.FileName.EndsWith(".bmp"))

{

System.Drawing.Image newImage;

MemoryStream stream = new MemoryStream(lc_ByteFile);

newImage = System.Drawing.Image.FromStream(stream);

newImage.Save("D:\\NewImage.bmp");

}

Try this out it works

Files in SQL 2005

Hello,
In my ASP.NET 2.0 web site I need to upload many files to various
folders and add the information in an SQL 2005 database.
I am creating a documents table which includes the fields:
[DocumentId], [DocumentAuthor] and [DocumentUrl]
This table will have records for many files.
Is there a standard way to name the files?
Maybe renaming the files to "doc" + DocumentId?
However my DocumentId is a Guid so it would be to big ... I think.
I would need to create the record, get the DocumentId and then access
the record again to add the DocumentUrl after renaming the file in
my .NET code.
Anyway, is there a standard way to make sure that every file name is
unique and that at the same time won't have an unreadable name?
Thanks,
Miguel
On Feb 14, 4:05 pm, "shapper" <mdmo...@.gmail.com> wrote:
> Hello,
> In my ASP.NET 2.0 web site I need to upload many files to various
> folders and add the information in an SQL 2005 database.
> I am creating a documents table which includes the fields:
> [DocumentId], [DocumentAuthor] and [DocumentUrl]
> This table will have records for many files.
> Is there a standard way to name the files?
> Maybe renaming the files to "doc" + DocumentId?
> However my DocumentId is a Guid so it would be to big ... I think.
> I would need to create the record, get the DocumentId and then access
> the record again to add the DocumentUrl after renaming the file in
> my .NET code.
> Anyway, is there a standard way to make sure that every file name is
> unique and that at the same time won't have an unreadable name?
> Thanks,
> Miguel
one possible solution make DocumentId as identity and store document
as documentid value
sql

Files in SQL 2005

Hello,
In my ASP.NET 2.0 web site I need to upload many files to various
folders and add the information in an SQL 2005 database.
I am creating a documents table which includes the fields:
[DocumentId], [DocumentAuthor] and [DocumentUrl]
This table will have records for many files.
Is there a standard way to name the files?
Maybe renaming the files to "doc" + DocumentId?
However my DocumentId is a Guid so it would be to big ... I think.
I would need to create the record, get the DocumentId and then access
the record again to add the DocumentUrl after renaming the file in
my .NET code.
Anyway, is there a standard way to make sure that every file name is
unique and that at the same time won't have an unreadable name?
Thanks,
MiguelOn Feb 14, 4:05 pm, "shapper" <mdmo...@.gmail.com> wrote:
> Hello,
> In my ASP.NET 2.0 web site I need to upload many files to various
> folders and add the information in an SQL 2005 database.
> I am creating a documents table which includes the fields:
> [DocumentId], [DocumentAuthor] and [DocumentUrl]
> This table will have records for many files.
> Is there a standard way to name the files?
> Maybe renaming the files to "doc" + DocumentId?
> However my DocumentId is a Guid so it would be to big ... I think.
> I would need to create the record, get the DocumentId and then access
> the record again to add the DocumentUrl after renaming the file in
> my .NET code.
> Anyway, is there a standard way to make sure that every file name is
> unique and that at the same time won't have an unreadable name?
> Thanks,
> Miguel
one possible solution make DocumentId as identity and store document
as documentid value

Files in SQL 2005

Hello,
In my ASP.NET 2.0 web site I need to upload many files to various
folders and add the information in an SQL 2005 database.
I am creating a documents table which includes the fields:
[DocumentId], [DocumentAuthor] and [DocumentUrl]
This table will have records for many files.
Is there a standard way to name the files?
Maybe renaming the files to "doc" + DocumentId?
However my DocumentId is a Guid so it would be to big ... I think.
I would need to create the record, get the DocumentId and then access
the record again to add the DocumentUrl after renaming the file in
my .NET code.
Anyway, is there a standard way to make sure that every file name is
unique and that at the same time won't have an unreadable name?
Thanks,
MiguelOn Feb 14, 4:05 pm, "shapper" <mdmo...@.gmail.com> wrote:
> Hello,
> In my ASP.NET 2.0 web site I need to upload many files to various
> folders and add the information in an SQL 2005 database.
> I am creating a documents table which includes the fields:
> [DocumentId], [DocumentAuthor] and [DocumentUrl]
> This table will have records for many files.
> Is there a standard way to name the files?
> Maybe renaming the files to "doc" + DocumentId?
> However my DocumentId is a Guid so it would be to big ... I think.
> I would need to create the record, get the DocumentId and then access
> the record again to add the DocumentUrl after renaming the file in
> my .NET code.
> Anyway, is there a standard way to make sure that every file name is
> unique and that at the same time won't have an unreadable name?
> Thanks,
> Miguel
one possible solution make DocumentId as identity and store document
as documentid value

File Shrinking

I have a data file that was created from an ISA web Proxy application that
is (being used by our networking department) 50 gig in size, and I would
like it to be around 5 gig if possible. The problem is that it was
initially creatred at 38 gig, so I cannnot shrink it below this amount. It
has no free space allocated!!! I thought i could even delete some hundred
thousand records from the one and only table in the database, but i can't
even do that without it timing out, or telling me the transaction log is
full. I'm using simple recovery, so i thought the log file would not grow
upon this huge deletion transaction but it did grow enormously. I then
disabled automatic file growth on the log, but it tells me the log file is
full and it won't perform the transaction. I have shrunk the transaction
log down to 500K, but still nothing. I'm using sql server 2005, with
windows xp.
Overall, how can i make this data file smaller? I have tried everything I
know with the transaction log, and manually deleting , but nothing seems to
work.
Error messages:
The transaction log for database 'ISAWebProxyLog' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases (this column says NOTHING)
or
A problem occurred attempting to delete row 1. Error source:
Microsoft.visualstudio.datatools. Error Message: The row values updated or
deleted either do not make the row unique or they alter multiple rows.
Correct the errors and attempt to delete the row again.That is why you should never create the files that large to begin with.
Create them relatively small then manually grow them to the size you need
for the next year or so. But in any case you can delete the rows in small
batches so that the tran log can be truncated in-between batches.
SET ROWCOUNT 50000
WHILE 1 = 1
BEGIN
DELETE FROM Table WHERE xxx
IF @.@.ROWCOUNT = 0
EXIT
END
SET ROWCOUNT 0
Another alternative is to export the data you wish to keep. Drop the DB and
create a new one with a smaller size. Then import the data back in.
Andrew J. Kelly SQL MVP
"Matt Fritz" <mafritz@.state.pa.us> wrote in message
news:eoSDUbHpHHA.5092@.TK2MSFTNGP04.phx.gbl...
>I have a data file that was created from an ISA web Proxy application that
> is (being used by our networking department) 50 gig in size, and I would
> like it to be around 5 gig if possible. The problem is that it was
> initially creatred at 38 gig, so I cannnot shrink it below this amount.
> It
> has no free space allocated!!! I thought i could even delete some hundred
> thousand records from the one and only table in the database, but i can't
> even do that without it timing out, or telling me the transaction log is
> full. I'm using simple recovery, so i thought the log file would not grow
> upon this huge deletion transaction but it did grow enormously. I then
> disabled automatic file growth on the log, but it tells me the log file is
> full and it won't perform the transaction. I have shrunk the transaction
> log down to 500K, but still nothing. I'm using sql server 2005, with
> windows xp.
> Overall, how can i make this data file smaller? I have tried everything I
> know with the transaction log, and manually deleting , but nothing seems
> to
> work.
> Error messages:
> The transaction log for database 'ISAWebProxyLog' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases (this column says NOTHING)
> or
> A problem occurred attempting to delete row 1. Error source:
> Microsoft.visualstudio.datatools. Error Message: The row values updated
> or
> deleted either do not make the row unique or they alter multiple rows.
> Correct the errors and attempt to delete the row again.
>|||The second error message you indicate lets me think that your table is an
Heap (no indexes nor primary key). In some cases this situation causes this
kind of error
Gilberto Zampatti
"Andrew J. Kelly" wrote:

> That is why you should never create the files that large to begin with.
> Create them relatively small then manually grow them to the size you need
> for the next year or so. But in any case you can delete the rows in small
> batches so that the tran log can be truncated in-between batches.
> SET ROWCOUNT 50000
> WHILE 1 = 1
> BEGIN
> DELETE FROM Table WHERE xxx
> IF @.@.ROWCOUNT = 0
> EXIT
> END
> SET ROWCOUNT 0
>
> Another alternative is to export the data you wish to keep. Drop the DB a
nd
> create a new one with a smaller size. Then import the data back in.
> --
> Andrew J. Kelly SQL MVP
> "Matt Fritz" <mafritz@.state.pa.us> wrote in message
> news:eoSDUbHpHHA.5092@.TK2MSFTNGP04.phx.gbl...
>
>

File Shrinking

I have a data file that was created from an ISA web Proxy application that
is (being used by our networking department) 50 gig in size, and I would
like it to be around 5 gig if possible. The problem is that it was
initially creatred at 38 gig, so I cannnot shrink it below this amount. It
has no free space allocated!!! I thought i could even delete some hundred
thousand records from the one and only table in the database, but i can't
even do that without it timing out, or telling me the transaction log is
full. I'm using simple recovery, so i thought the log file would not grow
upon this huge deletion transaction but it did grow enormously. I then
disabled automatic file growth on the log, but it tells me the log file is
full and it won't perform the transaction. I have shrunk the transaction
log down to 500K, but still nothing. I'm using sql server 2005, with
windows xp.
Overall, how can i make this data file smaller? I have tried everything I
know with the transaction log, and manually deleting , but nothing seems to
work.
Error messages:
The transaction log for database 'ISAWebProxyLog' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases (this column says NOTHING)
or
A problem occurred attempting to delete row 1. Error source:
Microsoft.visualstudio.datatools. Error Message: The row values updated or
deleted either do not make the row unique or they alter multiple rows.
Correct the errors and attempt to delete the row again.That is why you should never create the files that large to begin with.
Create them relatively small then manually grow them to the size you need
for the next year or so. But in any case you can delete the rows in small
batches so that the tran log can be truncated in-between batches.
SET ROWCOUNT 50000
WHILE 1 = 1
BEGIN
DELETE FROM Table WHERE xxx
IF @.@.ROWCOUNT = 0
EXIT
END
SET ROWCOUNT 0
Another alternative is to export the data you wish to keep. Drop the DB and
create a new one with a smaller size. Then import the data back in.
--
Andrew J. Kelly SQL MVP
"Matt Fritz" <mafritz@.state.pa.us> wrote in message
news:eoSDUbHpHHA.5092@.TK2MSFTNGP04.phx.gbl...
>I have a data file that was created from an ISA web Proxy application that
> is (being used by our networking department) 50 gig in size, and I would
> like it to be around 5 gig if possible. The problem is that it was
> initially creatred at 38 gig, so I cannnot shrink it below this amount.
> It
> has no free space allocated!!! I thought i could even delete some hundred
> thousand records from the one and only table in the database, but i can't
> even do that without it timing out, or telling me the transaction log is
> full. I'm using simple recovery, so i thought the log file would not grow
> upon this huge deletion transaction but it did grow enormously. I then
> disabled automatic file growth on the log, but it tells me the log file is
> full and it won't perform the transaction. I have shrunk the transaction
> log down to 500K, but still nothing. I'm using sql server 2005, with
> windows xp.
> Overall, how can i make this data file smaller? I have tried everything I
> know with the transaction log, and manually deleting , but nothing seems
> to
> work.
> Error messages:
> The transaction log for database 'ISAWebProxyLog' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases (this column says NOTHING)
> or
> A problem occurred attempting to delete row 1. Error source:
> Microsoft.visualstudio.datatools. Error Message: The row values updated
> or
> deleted either do not make the row unique or they alter multiple rows.
> Correct the errors and attempt to delete the row again.
>|||The second error message you indicate lets me think that your table is an
Heap (no indexes nor primary key). In some cases this situation causes this
kind of error
Gilberto Zampatti
"Andrew J. Kelly" wrote:
> That is why you should never create the files that large to begin with.
> Create them relatively small then manually grow them to the size you need
> for the next year or so. But in any case you can delete the rows in small
> batches so that the tran log can be truncated in-between batches.
> SET ROWCOUNT 50000
> WHILE 1 = 1
> BEGIN
> DELETE FROM Table WHERE xxx
> IF @.@.ROWCOUNT = 0
> EXIT
> END
> SET ROWCOUNT 0
>
> Another alternative is to export the data you wish to keep. Drop the DB and
> create a new one with a smaller size. Then import the data back in.
> --
> Andrew J. Kelly SQL MVP
> "Matt Fritz" <mafritz@.state.pa.us> wrote in message
> news:eoSDUbHpHHA.5092@.TK2MSFTNGP04.phx.gbl...
> >I have a data file that was created from an ISA web Proxy application that
> > is (being used by our networking department) 50 gig in size, and I would
> > like it to be around 5 gig if possible. The problem is that it was
> > initially creatred at 38 gig, so I cannnot shrink it below this amount.
> > It
> > has no free space allocated!!! I thought i could even delete some hundred
> > thousand records from the one and only table in the database, but i can't
> > even do that without it timing out, or telling me the transaction log is
> > full. I'm using simple recovery, so i thought the log file would not grow
> > upon this huge deletion transaction but it did grow enormously. I then
> > disabled automatic file growth on the log, but it tells me the log file is
> > full and it won't perform the transaction. I have shrunk the transaction
> > log down to 500K, but still nothing. I'm using sql server 2005, with
> > windows xp.
> >
> > Overall, how can i make this data file smaller? I have tried everything I
> > know with the transaction log, and manually deleting , but nothing seems
> > to
> > work.
> >
> > Error messages:
> > The transaction log for database 'ISAWebProxyLog' is full. To find out why
> > space in the log cannot be reused, see the log_reuse_wait_desc column in
> > sys.databases (this column says NOTHING)
> >
> > or
> >
> > A problem occurred attempting to delete row 1. Error source:
> > Microsoft.visualstudio.datatools. Error Message: The row values updated
> > or
> > deleted either do not make the row unique or they alter multiple rows.
> > Correct the errors and attempt to delete the row again.
> >
> >
>
>

File Shrinking

I have a data file that was created from an ISA web Proxy application that
is (being used by our networking department) 50 gig in size, and I would
like it to be around 5 gig if possible. The problem is that it was
initially creatred at 38 gig, so I cannnot shrink it below this amount. It
has no free space allocated!!! I thought i could even delete some hundred
thousand records from the one and only table in the database, but i can't
even do that without it timing out, or telling me the transaction log is
full. I'm using simple recovery, so i thought the log file would not grow
upon this huge deletion transaction but it did grow enormously. I then
disabled automatic file growth on the log, but it tells me the log file is
full and it won't perform the transaction. I have shrunk the transaction
log down to 500K, but still nothing. I'm using sql server 2005, with
windows xp.
Overall, how can i make this data file smaller? I have tried everything I
know with the transaction log, and manually deleting , but nothing seems to
work.
Error messages:
The transaction log for database 'ISAWebProxyLog' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases (this column says NOTHING)
or
A problem occurred attempting to delete row 1. Error source:
Microsoft.visualstudio.datatools. Error Message: The row values updated or
deleted either do not make the row unique or they alter multiple rows.
Correct the errors and attempt to delete the row again.
That is why you should never create the files that large to begin with.
Create them relatively small then manually grow them to the size you need
for the next year or so. But in any case you can delete the rows in small
batches so that the tran log can be truncated in-between batches.
SET ROWCOUNT 50000
WHILE 1 = 1
BEGIN
DELETE FROM Table WHERE xxx
IF @.@.ROWCOUNT = 0
EXIT
END
SET ROWCOUNT 0
Another alternative is to export the data you wish to keep. Drop the DB and
create a new one with a smaller size. Then import the data back in.
Andrew J. Kelly SQL MVP
"Matt Fritz" <mafritz@.state.pa.us> wrote in message
news:eoSDUbHpHHA.5092@.TK2MSFTNGP04.phx.gbl...
>I have a data file that was created from an ISA web Proxy application that
> is (being used by our networking department) 50 gig in size, and I would
> like it to be around 5 gig if possible. The problem is that it was
> initially creatred at 38 gig, so I cannnot shrink it below this amount.
> It
> has no free space allocated!!! I thought i could even delete some hundred
> thousand records from the one and only table in the database, but i can't
> even do that without it timing out, or telling me the transaction log is
> full. I'm using simple recovery, so i thought the log file would not grow
> upon this huge deletion transaction but it did grow enormously. I then
> disabled automatic file growth on the log, but it tells me the log file is
> full and it won't perform the transaction. I have shrunk the transaction
> log down to 500K, but still nothing. I'm using sql server 2005, with
> windows xp.
> Overall, how can i make this data file smaller? I have tried everything I
> know with the transaction log, and manually deleting , but nothing seems
> to
> work.
> Error messages:
> The transaction log for database 'ISAWebProxyLog' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases (this column says NOTHING)
> or
> A problem occurred attempting to delete row 1. Error source:
> Microsoft.visualstudio.datatools. Error Message: The row values updated
> or
> deleted either do not make the row unique or they alter multiple rows.
> Correct the errors and attempt to delete the row again.
>
|||The second error message you indicate lets me think that your table is an
Heap (no indexes nor primary key). In some cases this situation causes this
kind of error
Gilberto Zampatti
"Andrew J. Kelly" wrote:

> That is why you should never create the files that large to begin with.
> Create them relatively small then manually grow them to the size you need
> for the next year or so. But in any case you can delete the rows in small
> batches so that the tran log can be truncated in-between batches.
> SET ROWCOUNT 50000
> WHILE 1 = 1
> BEGIN
> DELETE FROM Table WHERE xxx
> IF @.@.ROWCOUNT = 0
> EXIT
> END
> SET ROWCOUNT 0
>
> Another alternative is to export the data you wish to keep. Drop the DB and
> create a new one with a smaller size. Then import the data back in.
> --
> Andrew J. Kelly SQL MVP
> "Matt Fritz" <mafritz@.state.pa.us> wrote in message
> news:eoSDUbHpHHA.5092@.TK2MSFTNGP04.phx.gbl...
>
>

File permissions

I've been having problem in my asp.net 2 web when using the "User Instance=true" style connections to databases.

I would get ASP.NET event log errors like this:
Exception information:
Exception type: SqlException
Exception message: An attempt to attach an auto-named database for file c:\temp\dnn\dnn\dnn455\App_Data\Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

After I used ProcessMonitor it showed that the user ASPNET was trying to access C:\ directly after trying to open the database.mdf file I wanted open.

I had to give the ASPNET account read/execute permission to c:\, which was very scary.

Could someone please enlightenment me as to the correct file permissions needed to use databases in user instances?

Thanks,
Alex

You have the option to connect the User Instance to Management Studio because of known issues with User Instance. Try the link below for details.

http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

File IO Exception thrown when trying to access a CLR Assembly in SQL Server

I have been encountering a problem using a CLR Assembly in SQL server. The Assembly is one provided by Microsoft for an example on using Exchange web services with SQL server.

http://www.microsoft.com/downloads/details.aspx?FamilyId=D6924897-7B62-46FD-874E-24FB0FBA2159&displaylang=en#Requirements

Essentially what this package is, is a set of c# classes that access the Exchange 2007 Web Services via a set of user defined functions and views in MS SQL Server 2005.

We have not modified the code except to configure for our host environment.

We are able to register the assembly using the setup.sql file included. But when we try to access any of the views or use the functions we get the following error:

Msg 10314, Level 16, State 11, Line 10

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65551. 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 'exchangeudfs, Version=0.0.0.0, Culture=neutral, PublicKeyToken=777b97dde00f3dbe' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

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)

We have 3 testing environments. 2 Windows 2003 servers called test and test2. Also a single Windows 2000 server called test3. Test and test2 have a full suite installed on them, web server, exchange 2007, sql server 2005, they are also domain controllers for their own domains.

We get the above error on test and test2 but it runs fine on test3. Test and test2 represent what our production environment is like. Test3 was just part of our troubleshooting process.

We have tried a lot to make this work. Here are some details on the things we have tried.

The database is set up to allow CLR Assemblies. This is part of the setup.sql.

The assembly's permission is set to external_access.

We have gone all the way to setting the file permissions on the dll to full control to the Everyone group.

We have tried different accounts to run the SQL Service. We've tried the system account, the local admin account and a seperate user account.

The database we are using is a fresh brand new database. Therefore it does not fit into the bug reported in this article:

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

I am really at a loss to where to go from here. Any ideas on why this 'out of the box' solution is causing us so many headaches would be appriciated.

Thanks,

Tim

Which box did you compile your changes on? Do the full assembly identities match for the exchangeudfs assembly and all the dependents?

|||

I compiled the assembly on another seperate development machine. I'm not sure I know how to answer your second question.

|||

Fro whatever reason I recompiled it and it magically started working. Not sure why.

File IO Exception thrown when trying to access a CLR Assembly in SQL Server

I have been encountering a problem using a CLR Assembly in SQL server. The Assembly is one provided by Microsoft for an example on using Exchange web services with SQL server.

http://www.microsoft.com/downloads/details.aspx?FamilyId=D6924897-7B62-46FD-874E-24FB0FBA2159&displaylang=en#Requirements

Essentially what this package is, is a set of c# classes that access the Exchange 2007 Web Services via a set of user defined functions and views in MS SQL Server 2005.

We have not modified the code except to configure for our host environment.

We are able to register the assembly using the setup.sql file included. But when we try to access any of the views or use the functions we get the following error:

Msg 10314, Level 16, State 11, Line 10

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65551. 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 'exchangeudfs, Version=0.0.0.0, Culture=neutral, PublicKeyToken=777b97dde00f3dbe' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

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)

We have 3 testing environments. 2 Windows 2003 servers called test and test2. Also a single Windows 2000 server called test3. Test and test2 have a full suite installed on them, web server, exchange 2007, sql server 2005, they are also domain controllers for their own domains.

We get the above error on test and test2 but it runs fine on test3. Test and test2 represent what our production environment is like. Test3 was just part of our troubleshooting process.

We have tried a lot to make this work. Here are some details on the things we have tried.

The database is set up to allow CLR Assemblies. This is part of the setup.sql.

The assembly's permission is set to external_access.

We have gone all the way to setting the file permissions on the dll to full control to the Everyone group.

We have tried different accounts to run the SQL Service. We've tried the system account, the local admin account and a seperate user account.

The database we are using is a fresh brand new database. Therefore it does not fit into the bug reported in this article:

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

I am really at a loss to where to go from here. Any ideas on why this 'out of the box' solution is causing us so many headaches would be appriciated.

Thanks,

Tim

Which box did you compile your changes on? Do the full assembly identities match for the exchangeudfs assembly and all the dependents?

|||

I compiled the assembly on another seperate development machine. I'm not sure I know how to answer your second question.

|||

Fro whatever reason I recompiled it and it magically started working. Not sure why.