i found one tutorial on self signing assemblies for use in sql server, but it appears to have errors. does anyone know of a better tutorial on this topic? the site im currently looking at is here: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx
View 1 Replies
Hi, a silly question here.
I wrote an assembly and attached to a trigger in one of my tables. It works as I expected, but then I decided to modify the code, say adding some new triggers that I will use with other tables. Then I realize that in order to update my new version of the assembly in SQL first I need to remove the trigger in the table, then remove the assembly, add the new version and finally run a script to create the trigger again in the table.
How could I avoid going through all this process everytime I want to update my assembly?
Thank you!
This is related to one of my previous posts.
I am running a CLR stored proc that goes to an EDS (Novell) server with LDAP and returns records into a SQL table.
I am using the Novell ldap library.
I want to do this with SSL so my code referneces the Mono security library as well.
However when I make the call to the stored proc to run in SSL, I get an object not found error. I do not think that the the Novell assembly can "find" the Mono assembly.
Two points:
1/ I can do the SSL if I run it as an asp.net page (so I know the SSL works)
2/ The proc runs and pulls all the records in non-SSL (so I know the proc works)
Any ideas?
Thanks,
BIG
I want to use a custom assembly in my report expression.
I have created a class library and built it. Then what do I do next.
Can yu be a little more explicit since I am a novice.
Thanks
Sai
I have a question: how is it planned to replicate changes made to the assemblies and to the CLR functions or UDT? Will Visual Studio deploy change of the Assembly as ALTER not DROP/CREATE?
View 1 Replies View RelatedGood morning,
How does SQL Server 2005 handle the replication of assemblies? I get the following when trying to replicate an assembly:
Timestamp: 6/15/2007 9:41:33 AM
Message: HandlingInstanceID: 05eaed53-b6be-42da-8c72-0f91fcbfa5b6
An exception of type 'Microsoft.SqlServer.Replication.ComErrorException' occurred and was caught.
-------------------------------------------------------------------------------------------------
06/15/2007 09:41:33
Type : Microsoft.SqlServer.Replication.ComErrorException, Microsoft.SqlServer.Replication, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Message : The schema script 'StratisDSonetd85d6e89_1061.sch' could not be propagated to the subscriber.
Source : Merge Replication Provider
Help link :
ErrorMessage : Unspecified error
InterfaceId : 00000000-0000-0000-0000-000000000000
HelpFile :
HelpContext : 0
Description : The schema script 'StratisDSonetd85d6e89_1061.sch' could not be propagated to the subscriber.
ComHResult : -2147467259
ErrorCode : -2147467259
Data : System.Collections.ListDictionaryInternal
TargetSite : Void Run()
Stack Trace : at Microsoft.SqlServer.Replication.MergeSynchronizationAgent.Run()
at Microsoft.SqlServer.Replication.MergeSynchronizationAgent.Synchronize()
at SQLClientSync.SQLClientSync.Replicate()
Additional Info:
MachineName : REPCLIENT
TimeStamp : 6/15/2007 1:41:33 PM
FullName : Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=1.1.1.1, Culture=neutral, PublicKeyToken=d2ff10320a77ec18
AppDomainName : sqlclientsync.exe
ThreadIdentity :
WindowsIdentity : REPCLIENTAdministrator
I appears that the initial replication of the assembly was fine, when re-running the sync (no changes to the assembly), this error is generated.
Thanks...
Hi Guys,
I have a website with multiple pages and when a user signs in it puts a 1 in the database and their online status is shown to the rest of the members, if they use my signout button which I have on each page, it signs them out and updates the database with a 0 in the process, so shows them as then offline, my problem is some people forget to sign out and just close the browser window or begin surfing elsewhere.
Is there a way to have sql server 2000 automatically change the field in the database after 30 minutes of inactivity.
Or is there a way to automatically sign users out as they either click on the X in the browser window, or as they leave my site.
Any help would be appreciated.
Cheers
Hello,
Is there any built in function to find out the sign of a value.
For Ex: -225.89 Sign is -
566.987 Sign is +
Thanks
Ganesh Kumar
Solutions are easy. Understanding the problem, now, that's the hard part
I typed the following query
ALTER TABLE [konnik].[PORTAL_CONFIG]
ADD CONSTRAINT [DF_PORTAL_CONFIG_AUTOPM_ON]
DEFAULT (1) FOR [AUTOPM_ON]
and i received the following warning
Warning: The table 'PORTAL_CONFIG' has been created but its maximum row size (12068) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Bear in mind that this table come from Access through the Dts. Could anyone translate this for me? How can i overcome this???
Hello:
I need to change minus brackets () with - sign in SSRS. How i can do this?
Thanks
Amit
How to add assemblies to Sql server 2005 to do some user defined function......with some example code..........
View 1 Replies View Related
Hi all,
I need to dynamically create a ADO.NET connection manger
so, my question is if it is safe to create it with the string that is generated (ConnectionManagerType) when creating the connection manager manually on my PC.
I understood that this string is the full qualified name of the assembly, so will it be different when I create it on another P.C or it is fixed on all P.Cs ?
Thanks ahead
Eran
I have followed the steps outlined in the knowledge base article http://support.microsoft.com/kb/913668 for effecting Xml Serialization within the SQL CLR. That is, I have
1. Prebuilt the serialization assembly X.Serializers for the types in assembly X and,
2. Registered both assemblies with SQL Server via the create assembly directive
Yet, when I attempt to create an XmlSerializer on the basis of one of the types defined in X, SQL CLR ignores the pre-built serialization assembly and attempts to dynamically create/load the assembly. Since dynamic loading is disallowed, this fails with the expected exception:
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.CompileAssemblyFromSourceBatch(Com
...
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 Cypress.Services.Client..
What do I need to do the force the runtime to load the pre-built serialization assembly instead of dynamically trying to create one?
Thank You,
Chris.
Hi,
It's possible to register an assembly in SqlServer 2005 using the
CREATE ASSEMBLY
syntax.
How can I get a list of assemblies that have been registered?
Thanks
Herbjörn
Where is it documented what the list of approved assemblies are for SQL 2005. Also, is there a way via T-SQL, or other means to get it programatically?
View 4 Replies View RelatedI have two questions.
How can I run my C++ assembly in safe mode?
I'm building a SQL function from C++ and compile my assembly with /clrafe. After that I create the assembly with SAFE permission sets and create the function, both in SQL. But the server don't accept load or execute the function because of permission error. It says:
Code Snippet
"An error occurred in the Microsoft .NET Framework while trying to load assembly id 65561. The server may be running out of resources, or the assembly may not be trusted with PERMISSIONSET = EXTERNALACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues."
Does the terminology coince with the technology, or is it diffrent "safety" conditions we are speaking about? One for the SQL permission set, and another safety for CLR type-safe assemblies?
Anyway, after playing around a bit, and following the tricks from this thread. I managed to load and execute the function, but only in UNSAFE permission (unrestricted mode?)
---8<----
My second question is can I use unrestricted mode for assembly compiled with /clr (mixed CLR)?
I'm asking, because I have some C source code that I want to reuse, and for this I can't be type-safe and therefore need to compile only with /clr (mixed CLR).
Should I go back to the plain C API in SQL 2000 to implement such function in SQL 2005, and ignore all new things in c++/CLR/.NET?
If you have any points to C++ sample code, then please forward this to me.
I have followed the steps outlined in the knowledge base article http://support.microsoft.com/kb/913668 for effecting Xml Serialization within the SQL CLR. That is, I have
1. Prebuilt the serialization assembly X.Serializers for the types in assembly X via the SGEN tool and,
2. Registered both assemblies with SQL Server via the create assembly directive
Yet, when I attempt to create an XmlSerializer on the basis of one of the types defined in X, SQL CLR ignores the pre-built serialization assembly and attempts to dynamically create/load the assembly. Since dynamic loading is disallowed, this fails with the expected exception:
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.CompileAssemblyFromSourceBatch(Com
...
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 Cypress.Services.Client..
What do I need to do the force the runtime to load the pre-built serialization assembly instead of dynamically trying to create one?
Thank You,
Chris.
Where are CLR Assemblies stored on SQL Server when compiled from a SQL Server Project?
Are they stored in a specific folder or stored inside the database?
Does deleting the Assembly under Programmability/Assemblies remove the Assembly from the server?
Thanks.
I have a field in table A named tbl_dob.It is date of birth of member. I want to find zodic sign from this tbl_dob.How I get it.Please help me.
View 1 Replies View RelatedHi
I have come across a bit of problem with my Web Form. I have standard textbox which inserts a value into a SQL 2000 database. However if I put a pound sign anywhere inside the text box and do the insert it disappears when I try and reshow the value.
I have checked the database field to see if it has been inputted but its no where to be seen.
So what has happened to my pound sign? any ideas?
I am using VS.Net 2003.
Thanks in advance
I have a SQL 7 db that I use a DTS package to import Oracle data into. The package works fine and imports all the appropriate data. However, if I use an Access 2000 database to attach to the data via ODBC (using the MS SQL Server driver), the negative sign is dropped when displaying data in a table, query, or report.
Same problem in SQL Server - if I query the SQL 7 data via the Query Analyzer, the negative signs are dropped. However, if I query the SQL data using the Enterprise Manager (i.e., Open Table...Return All Rows via right click on the table), the data shows up properly with the negative signs there. Bottom line - the data is correct, but doesn't get displayed correctly in QA or via ODBC.
What gives?! Can anyone explain to me the "connections" that occur between EM and QA? Looks like QA uses a "temporary" ODBC connection to talk to the data, while the EM connects "directly" to the data. Also, what gives with the MS SQL Server ODBC driver - why wouldn't it display the negative signs? Is there a better SQL Server ODBC driver that I should/could use? I've tried configuring the ODBC connection differently, but to no avail.
Any help is greatly appreciated, as the data in question is being used in court and absolutely HAS to be accurately displayed.
Thanks!
Jeff Jones
Atlanta, GA
Does anyone know to include the Euro sign in SQL statements. I found an Ascii
code of ALt+0128, but then I use CHAR (0128) it actually prints the Char (128) which is Ç not €.
When I use the € directly in the code the application just shows a ?
Any help would be highly appreciated.
Markus
Code:
objrs.Open "Select * from [klcc_f$]", objConn, adOpenStatic
why IM getting dollar sign is not a valid name ?
I am trying to use this command to change the variable in where clause each time:
psexec servername -E cmd.EXE /c "sqlcmd -S servername /E -d dbname -v entertext="'%anything%'" -i c:myfoldermyscript.sql -o c:mypathoutput.sql"
myscript.sql is like:
select * from table_name
where summary like $(entertext);
This command works just for variables with single and exact word. It does not work for variables which has space between them or when I enter part of summary.In fact, I wanted to write something which the user be able to enter variable each time when he/she run this batch file. because I put this sqlcmd command in batch file( like this in oracle: "select * from table_name where summary = &entertext; " I was looking the same command in sql server , but I did not find it). I do not know .net or any other programing language( I know just csh and command line). I want to do this just with sql command.
I have a query where I have to use the convert function to return the unitprice as a decimal with 2 digits to the right of the decimal and a $ to the left of the number like 10 should be $10.0o. How do i insert the $. this is my code so far:
SELECT CONVERT(varchar, UnitPrice, 1) AS varchartotal
FROM dbo.[Order Details]
I'm trying to import a text file which contains dates and decimal values. My problem is that the date has the following format "YYYY-MM-DD" and the decimal sign for the values are "." (dot).
If I change the "Locale" for the file source so it will handle the "." signs in the values then the dates are invalid, and if I have it so the dates are handled then the "."s aren't seen as decimals.
Is it posible to handle this problem in SSIS
Thanks
In Crystal Reports, there was a "reverse sign for display" property that could be checked for a number box. This was used for debits and credits (and such) so a negative number would be displayed as a positive and a positive displayed as a negative.
How would this work, or is there something simple, in Reporting Services?
Thanks for the information.
Just lately I have noticed that I am periodically getting signed out of this forum. When I try and sign back in I get directed to an error page saying:
We apologize, but an unknown error has occured in the forums.
This error has been logged.
I would say this has been happening for a couple of weeks now at least.
Has anyone else expereienced this?
Thanks
Jamie
Hi,
I did all the step mentioned in this MSFT article http://msdn2.microsoft.com/en-us/library/ms137947.aspx Yet I can not sign a package using a certificate.
To sign a package
In Business Intelligence Development Studio, open the Integration Services project that contains the package you want to work with.
In Solution Explorer, double-click the package to open it.
In SSIS Designer, on the SSIS menu, click Digital Signing.
In the Digital Signing dialog box, click Sign.
The Select Certificate dialog box, select a certificate.
Optionally, click View Certificate to view certificate information.
Click OK to close the Select a Certificate dialog box.
Click OK to close the Digital Signing dialog box.
To save the updated package, click Save Selected Items on the File menu.
I don't see a certificate to select after following the article's steps. Can some one please shed some light on this?
Thanks,
Saptagiri Tangudu
Hi everyone,
I am testing reporting services custom assembly feature, but have problems.
I have referenced my dll, put my class name and instantiated it...
when i try and use a method within my instantiated class, it says "file or assembly name was not found".
Any ideas?
Did this as a exercise and am fed up with my fruitless searching.Went and create an CLR assembly and all with min hassel. Now that Ihave that dll out there, I am tring to find some system table or somedisplay in management studio that shows me where the file is located onthe hard drive and cannot locate it.Can someone please point me in the correct location.Thanks.
View 1 Replies View RelatedHi guys,
I'm trying to write an SSIS script that will create an excel file and append data to it.
I downloaded the Office 2007 PIAs (Primary Interop Assemblies) and installed them on my desktop PC where i'm developing from.
However, and even though the assemblies are in the GAC, they don't appear when you look in "Add Reference" for your script task.
If i create a standard .NET web or windows app and try to "Add Reference" I can find them there.
Am I missing anything obvious?
Thanks for your help in advance
Mike
I am trying to load multiple strongly named assemblies into the same database which are signed with the same .snk file (signed in Visual Studio). I use the following code to create an asymmetric key and login as Books Online recommends:
CREATE ASYMMETRIC KEY SQLCLRKey FROM FILE = 'D:dbainAssembly.dll'
CREATE LOGIN CLRAssembler FROM ASYMMETRIC KEY SQLCLRKey
GRANT UNSAFE ASSEMBLY TO CLRAssembler
GRANT EXTERNAL ACCESS ASSEMBLY TO CLRAssembler
REVOKE CONNECT SQL FROM CLRAssembler
Do I need to create a new login and asymmetric key for each assembly I load? If so, do I need to sign each with a different key because its giving me an error message when I try to create 2 separate asymmetric keys/logins from 2 different assemblies which have been signed with the same .snk file.
The only way I've gotten everything to load properly is to create a separate key for each assembly and sign each, then create separate logins and asymmetric keys in the database.
Is this the only way to do this? Or am I missing something?