CLR procedure implementation

hi,

i have created a class library to validate the pattern of regular expression.

now how do i call it in an t-sql program so that the class library will read data from database and return the appropriate value?

i am trying to integrate the clr procedure.. but somehow i aint confident, about passing the parameters,

chaman!

Hi,

there are several samples out there:

http://msdn2.microsoft.com/en-us/library/ms131094.aspx (in this case with an additional output parameter)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

CLR procedure calling webservice

Hi, I want to create following procedure to call a webservice. build ok
execution not ok.
When i do it in a seperated program it works. in the clr procedure not.
It always end with 'System.InvalidOperationException' occurred in
System.Xml.dll
Can some one help me.
Ludo
SQL code:
exec dbo.SendStatusToWebservice 'SQL2K5','TEST Ludo','GREEN'
.Net code
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Diagnostics;
public partial class CLR_Procedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
}
public static void SendStatusToWebservice(SqlString MyAppl, SqlString
MyMessage, SqlString MyStatus)
{
string log;
// Connect to webservice and add logging to it
SQL_UDP.bgc.wss.Library wlib = new SQL_UDP.bgc.wss.Library();
wlib.Credentials = System.Net.CredentialCache.DefaultCredentials;
log = wlib.WSScreateLog(MyAppl.Value);
wlib.WSSwriteLog(log, MyMessage.Value);// + " at @. " +
DateTime.Now.ToString);
wlib.SetBatchStatus(MyStatus.Value, log);
}
};
Debug result:
Auto-attach to process '[3068] [SQL] bgc-mikmxeue486' on machine
'bgc-mikmxeue486' succeeded.
Debugging script from project script file.
The thread 'bgc-mikmxeue486 [61]' (0xd60) has exited with code 0 (0x0).
The thread 'bgc-mikmxeue486 [61]' (0xd60) has exited with code 0 (0x0).
The thread 'bgc-mikmxeue486 [61]' (0xd60) has exited with code 0 (0x0).
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
Code' is enabled.
Auto-attach to process '[3068] sqlservr.exe' on machine 'bgc-mikmxeue486'
succeeded.
'sqlservr.exe' (Managed): Loaded 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Binn\SqlAccess.dll', Skipped loading symbols. Module is
optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.
dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
Code' is enabled.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
Code' is enabled.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\Syst
em.Transactions.dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
Code' is enabled.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_MSIL\System.Security\2.0.0.0__b03f5f7f11d50a3a\System
.Security.dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
Code' is enabled.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.
dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'SQL_UDP', No symbols loaded.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_MSIL\System.Web.Services\2.0.0.0__b03f5f7f11d50a3a\Sy
stem.Web.Services.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\S
ystem.Configuration.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'WebserviceCLR', Symbols loaded.
A .NET Framework error occurred during execution of user defined routine or
aggregate 'CallWebservice':
System.InvalidOperationException: Cannot load dynamically generated
serialization assembly. In some hosting environments assembly load
functionality is restricted, consider using pre-generated serializer. Please
see inner exception for more information. --> System.IO.FileLoadException:
LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by
the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[]
rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean
fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[]
rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters
options, String[] fileNames)
at
Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters
options, String[] sources)
at
Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.C
ompileAssemblyFromSourceBatch(CompilerPa
rameters options, String[] sources)
at
System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerPa
rameter
..
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns,
CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[]
xmlMappings, Type[] types, String defaultNamespace, Evidence evidence,
CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings,
Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[]
mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at WebserviceCLR.wsslib.Library...
No rows affected.
(0 row(s) returned)
Finished running sp_executesql.
A first chance exception of type 'System.InvalidOperationException' occurred
in System.Xml.dll
The thread 'bgc-mikmxeue486 [61]' (0xd60) has exited with code 0 (0x0).
The program '[3068] [SQL] bgc-mikmxeue486: bgc-mikmxeue486' has exited with
code 0 (0x0).
The program '[3068] sqlservr.exe: Managed' has exited with code 259 (0x103)."examnotes" <Ludo@.discussions.microsoft.com> wrote in
news:825F081A-11D6-4D36-8C9A-F8B4AEA96CE5@.microsoft.com:

> Hi, I want to create following procedure to call a webservice. build
> ok execution not ok.
> When i do it in a seperated program it works. in the clr procedure
> not. It always end with 'System.InvalidOperationException' occurred
> in System.Xml.dll
> Can some one help me.
>
[snip]

> In some hosting
> environments assembly load functionality is restricted, consider using
> pre-generated serializer. Please see inner exception for more
> information. --> System.IO.FileLoadException: LoadFrom(), LoadFile(),
> Load(byte[]) and LoadModule() have been disabled by the host.
> System.IO.FileLoadException:
As the error says, SQLCLR doesn't allow you to load a dynamically
generated assembly (which happens when you do web-services). You need to
sgen the proxy code into a dll and catalogue that assembly in SQL
Server.
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
****************************************
**********

Client user timeout

Hi,
Used Mssql 5 exp. On win 2003 server
my problem,
client user time out 15 minute by sql server,
Client user inside my program, connection closed,
logout program and relogin program work again,
no network problem, no closed network connection
How can i do,There are several reasons depending upon how the connection process is set
up. For a brief review see: http://vyaskn.tripod.com/watch_your_timeouts.htm
Anith

Client Machine accesing sqlexpress

This may a silly question but....

I wrote a vb 2005 application that access sqlexpress for data using sqlconnection (.NET). The program works fine. When I run debug on my code that is in my laptop, it access my xp server box with sqlexpress running and I see all my data in my application. My question is..When I compile this program and install it on a client machine that has no sqlexpress tools, no SQLCMD etc....will the application run and access the xp server box, or do I have to install something on the client machine.

thanks

hi,

your "remote client pc" does only need your app and the .Net Framework, which installs all the required libraries to connect to local and remote SQLServer instances..

regards

Client access to sql database

sql 2000 on 2003 server standard, only 2 out of 10 users (xp pro clients)
trying to access sql program are asked to login to database, server name,
database, login id, password to TableName: datatable.
Is this an xp client issue or sql issue.
Can you give more details? How exactly are they logging in? Are you using
Windows authentication or SQL? Windows won't prompt you to login to SQL
Server since you already logged in to the domain.
Andrew J. Kelly SQL MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:FB02C0E3-2E67-4979-B809-C49BE0496BCA@.microsoft.com...
> sql 2000 on 2003 server standard, only 2 out of 10 users (xp pro clients)
> trying to access sql program are asked to login to database, server name,
> database, login id, password to TableName: datatable.
> Is this an xp client issue or sql issue.
|||Users logon to the domain, and from a shared drive the clients run an msi
file that installs a program on their local pc's, this uses a sql database.
They start the program and when they try to run a report they get a pop up
box asking for login info for "TableName datatable". I think it is Windows
Authentication, don't know how to check which one is being used.
Trying to determine if an xp client issue or sql issue.
"Andrew J. Kelly" wrote:

> Can you give more details? How exactly are they logging in? Are you using
> Windows authentication or SQL? Windows won't prompt you to login to SQL
> Server since you already logged in to the domain.
> --
> Andrew J. Kelly SQL MVP
>
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:FB02C0E3-2E67-4979-B809-C49BE0496BCA@.microsoft.com...
>
>
|||If it is windows authentication it should not need to ask. You really need
to see what the application is using for a connection string.
Andrew J. Kelly SQL MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:BEF206D6-AA2F-4949-9D5C-95B13AE16D42@.microsoft.com...[vbcol=seagreen]
> Users logon to the domain, and from a shared drive the clients run an msi
> file that installs a program on their local pc's, this uses a sql
> database.
> They start the program and when they try to run a report they get a pop up
> box asking for login info for "TableName datatable". I think it is Windows
> Authentication, don't know how to check which one is being used.
> Trying to determine if an xp client issue or sql issue.
> "Andrew J. Kelly" wrote:
|||Unfortunately I'm not a sql guru, just a network admin. This program that
points to the database was created by an outside company. Of course they say
that because only 2 of clients have the login issue it must be an xp client
issue and has nothing to do with the program. But the users are already in
the program, it is only when they try to run a report from within the program
that the login box appears?
Got any opinions one way or another?
"Andrew J. Kelly" wrote:

> If it is windows authentication it should not need to ask. You really need
> to see what the application is using for a connection string.
> --
> Andrew J. Kelly SQL MVP
>
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:BEF206D6-AA2F-4949-9D5C-95B13AE16D42@.microsoft.com...
>
>
|||What ever it is that is attempting to run the report must be making a new
connection. But without knowing what or how it is attempting to do this it
is anyone's guess. The vendor must have a clue as to how they connect and
why this is happening. After all they wrote it<g>. You can try using
profiler to see what they are sending to sql server at the time it happens.
Other than that I don't have a clue as to what the app is really doing.
Andrew J. Kelly SQL MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:A498DAFD-838E-466C-A03F-FC3102784162@.microsoft.com...[vbcol=seagreen]
> Unfortunately I'm not a sql guru, just a network admin. This program that
> points to the database was created by an outside company. Of course they
> say
> that because only 2 of clients have the login issue it must be an xp
> client
> issue and has nothing to do with the program. But the users are already in
> the program, it is only when they try to run a report from within the
> program
> that the login box appears?
> Got any opinions one way or another?
> "Andrew J. Kelly" wrote: