InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 17 May 2008 09:13:17 GMT20http://www.sqlservercentral.com/Forums/Topic501897-386-1.aspxHiI have written a library of stored procedures in C# that p/invoke a dll written in Delphi for Win32. The reasone for this is that the dll contains license key validation routines. I.e. the stored procedures check that the license is valid before executing. The reason that the code is written in delphi for win32 is due to the nature of the license key, and how it is built, etc.But when I go to deploy the Stored procediure library I get the following error:CREATE ASSEMBLY failed because type "KMS.Interop.CheckLicense" in safe assembly "KMS_StoredProcedures" has a pinvokeimpl method. P/Invoke is not allowed in safe assemblies.But when I set the database permision level to EXTERNAL I get the following error:CREATE ASSEMBLY for assembly 'KMS_StoredProcedures' failed because assembly 'KMS_StoredProcedures' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.I have tried searching MSDN but the documentation is vague as to how to resolve this. I have tried the adventure works example by using the Visual Studio SN command line tool and signing my library with the key generated.Any help would be greatly apprieciated!!! as I do not want to write all the insert/deletes/updates into the application.TIA Graham HarrisFri, 16 May 2008 04:51:50 GMTgharris_35http://www.sqlservercentral.com/Forums/Topic478505-386-1.aspxAny tips, links anyone has to share on the subject of SQL CLR and webservices, especially when it comes to security?Our devs want to deploy SQLCLR functionality that utilizes web services, and I know nothing about the subject! :w00t:It seems I've opened pandora's box by enabling SQLCLR on our systems...Wed, 02 Apr 2008 07:51:48 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic501711-386-1.aspxI am trying to use a CLR procedure inside an import script. I thought it would be easiest to put it in a function so that I wouldn't have to complicate the import script by declaring input and output variables. However, I was confronted by the message "Only functions and extended stored procedures can be executed from within a function."What I don't understand is why can you use extended stored procedures but not CLR procedures? I am not updating any tables inside the CLR procedure or even using a contextual connection at all.Can someone please explain the reasoning behind this to me?Thu, 15 May 2008 16:47:27 GMTghelyarhttp://www.sqlservercentral.com/Forums/Topic498918-386-1.aspxWhat is the best way to implement a centralized library of SQL CLR objects (call it LIBRARY), so that they can be referenced from every SQL instance in a production enterprise?I have such a library of objects for file manipulations, such as list folder contents, copy, move, delete files etc. In the future I'm thinking of adding regex functions, as well as custom aggregate types to it.I'm thinking of creating the LIBRARY database in a single server instance and somehow regularly 'replicate' the objects across the different server instances where they will be referenced. This is so that changes to these objects in the centralized location will be sync'ed across all instances.How could this kind of "replication" work? Perhaps do daily restores on each instance from the previous night's backup?Any advice from anyone that attempted something like this before?Mon, 12 May 2008 09:30:39 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic501818-386-1.aspxHI ALL DBA, DEVLOPERS, I AM NEW USER OF MS SQL SERVER 2005.I WANT YOUR HELP FOR XML IN MS SQL SERVER 2005.PLS HELP ME FROM BEGINNING OF XML IN SQL .THANK YOU ALL OF YOU.REGARDS,MAYURFri, 16 May 2008 01:32:37 GMTmayurshttp://www.sqlservercentral.com/Forums/Topic497926-386-1.aspxHi. I'm getting a headache trying to solve a mysterious problem.I have developed some custom CLR aggregate functions that return all the strings contained in a column, separated by a '<br>' or a ' - ' or other strings.I did this in Visual Studio 2005 one year ago and it has worked perfectly until today.Now I needed to add a custom CLR function and I have migrated the project to Visual Studio 2008.I have added the new function and deployed it to my development SQL server in DEBUG mode. Everything works fine.Then I deployed everything on the server (always from within VS2008), and the toy is broken. Note that the functions are used by ASP.NET, accessing SQL through integrated authentication.If I open a page that uses the aggregate with my personal account (I am the DBA), everything works fine. If users open the page, they get an [i][b]EXECUTE authorization denied on the object 'MyCustomAggregate'[/b][/i] error message.I tried everything. I tried changing the authorisation level from SAFE to EXTERNAL to UNSAFE. I tried changing the assembly owner from 'dbo' to my account. Of course the db is 'trustworthy' (otherwise the CLR assembly wouldn't have worked until today).The funny thing is that the new function (a simple scalar function) works perfectly. All I have to to is to right click on its name in SQL Management Studio, and grant the EXECUTE privilege to the 'public' role. This means that the problem is [b]not[/b] a matter of assembly security (which was also clear from the fact that the aggregates work if I query them from my account).It must be a matter of giving the EXECUTE authorisation to the other users. How can I do that??? Seems like there are no individual permissions on the aggregates. Running a [code]GRANT EXECUTE ON [dbo].[MergeStringSepBR] TO [TheRoleIWant][/code] gives an error.For the sake of completeness, I give you the code of one of my failing aggregates:[code]using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000, IsInvariantToDuplicates = false, IsInvariantToOrder = false, IsInvariantToNulls = true, IsNullIfEmpty = false)]public struct MergeStringheSepBR : IBinarySerialize{ public void Init() { accu = ""; } public void Accumulate(SqlString Value) { if (!Value.IsNull) { if (accu == "") accu = Value.Value; else accu += "<br>" + Value.Value; } } public void Merge(MergeStringheSepBR Group) { accu += "<br>" + Group.accu; } public SqlString Terminate() { // Inserire qui il codice return new SqlString(accu); } // Campo di membro segnaposto private string accu; #region IBinarySerialize Membri di void IBinarySerialize.Read(System.IO.BinaryReader r) { accu = r.ReadString(); } void IBinarySerialize.Write(System.IO.BinaryWriter w) { w.Write(accu); } #endregion}[/code]Fri, 09 May 2008 08:17:46 GMTdavidthegrayhttp://www.sqlservercentral.com/Forums/Topic498121-386-1.aspxI'm having a problem executing a SQLCLR function: this function calls a web services that processes a query to a data base and returns a table to be used in a stored procedure. In a low concurrency scenario (not to many clients connected), the function returns correctly, however when the concurrency level is increased we have a SQLCLR command execution problem (all the SQLCLR processes hangs), making the server unavailable to all web services processes.At first we thought the problem could be the SQLCLR, since the web services is 100% available, all the time. We monitored to come to this conclusion. Do you know of some SQLCLR bug?Could someone help me with this? I'm in a difficult situation with my client, considering that we defended the MS SQLServer technology and now it's not working properly.Att,Assis MarquesDBAFri, 09 May 2008 11:39:29 GMTassismarqueshttp://www.sqlservercentral.com/Forums/Topic501146-386-1.aspxCan any one of you give me a sample to convert the SQL table-valued function to CLR table-valued function using C#.NetThu, 15 May 2008 04:16:55 GMTpreetha.sathyamoorthyhttp://www.sqlservercentral.com/Forums/Topic500935-386-1.aspxOn page 82 of the book is code to define a table valued function. I cannot get it to work using VS 2008 Professional. Any one help? I have tried going to the Wrox Site with previous questions, but have given up on them because neither the authors or anyone else offered any help. Specifically: here are the lines on page 82 that I am having trouble with. 1.The line: public static IEnumerable ... needs something to work, but I don't know what to do with it. At build time I get the following error message:Error 1 Using the generic type 'System.Collections.Generic.IEnumerable ' requires '1' type arguments 2.The line: if (File.Exists(FilePath)) There is something wrong because "File.Exists" never is offered by intellisense. I'm probably missing something but don't know what.Help on either of the above please.---------------------------------------------------------Additionally, I have found after a lot of head scratching the following fixes to this code. 1.First line: namespace SQL CLRRoutines.FileIO - I'm guessing that there should be a period between SQL and CLR... but that is a guess. This is screwy because SQL.CLRRoutines.FileIO is never used. 2.There is a missing using statement for System.Collections.Generic, othewise you cant even reference IEnumerable.3.The decoraton statement [Microsoft.SqlServer.Server.SqlFunction... ] doesn't work typed exactly as shown. Type it all on one line and omit the plus sign.Victor Victor (kayuca)After submitting above, I took a guess at what IEnumerable wants and changed that line toIEnumerable which I believe it wants to enumerate thru -- strings. Yeah - It compiles. That's the good news. The bad news is I get the orignal error when I try to deploy it. HELP!!Following is my complete code for this tvf:using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Collections.Generic;namespace SQL.CLRRoutines.FileIO{ sealed public partial class TableValuedFunctions //Sealed means the class cannot be inherited. { [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="tvf_ParseFile_FillRow",TableDefinition="Item NVARCHAR(4000)")] public static IEnumerable tvf_ParseFile(String FilePath, char Delimiter) { //Declare local variables String[] sItems; System.IO.StreamReader oSR; //if (FilePath.Exists(FilePath)) if(FilePath.Length > 0) { oSR = System.IO.File.OpenText(FilePath); //parse file sItems = oSR.ReadToEnd().Split(Delimiter); //close file. oSR.Close(); //return items. return (sItems); } else { throw new System.Exception("File not found"); } }//end of method. private static void tvf_ParseFile_FillRow(Object obj, out SqlString sItem) { String sTemp = (string)obj;//convert object. sItem = sTemp; //assign value to field. }//end of method. }//end of class.}//end of namespace.Wed, 14 May 2008 15:43:49 GMTkayucahttp://www.sqlservercentral.com/Forums/Topic499132-386-1.aspxI need to change a string in a .txt file using .NET, the .NET code will be executed by an SSIS package and the file needs to be changed so it can be used by another process which looks at that specific line (line 9 value: 'set ASOF=20080424'), for that specific date string.What method in .NET can I use to perform this task? Should be very simple, correct?Thanks in advance for the help!Mon, 12 May 2008 14:39:18 GMTIgor Santoshttp://www.sqlservercentral.com/Forums/Topic461814-386-1.aspxHi,I am a DBA new to CLR and I have a developer who would like to start using CLR. I have started to research CLR but I have not yet found anything on what permissions a SQL Server login needs to use CLR (db_owner, Proxy Account, etc). I know if I grant SA to a login then the developer can work with CLR but granting SA to each developer is not something I want to do.Thanks,DavidThu, 28 Feb 2008 09:27:20 GMTdemmetshttp://www.sqlservercentral.com/Forums/Topic498319-386-1.aspxI am creating a trigger that is fired after an insert or update.If it is an insert and value1 = value2 then I need to add a row to a table with values from another tableIf it is an update and value1 = 0 then I need to add a row to a table with value from another tableWhere do i start?Fri, 09 May 2008 22:41:25 GMTComputerStudenthttp://www.sqlservercentral.com/Forums/Topic493537-386-1.aspxOK I felt like trying to make a CLR project that does RTF to Text conversion.Everyone says it's so simple, and yet I'm stuck on the most trivial of issues.If I create a New Visual Basic >> Windows Application Project in Visual Studio 2005, I can use this command to create an object no problem:dim rtfObj as New System.Windows.Form.RichTextboxIf I create a New Visual Basic >>Database >>SQL Server Project , the same command does not let me reference System.Windows, let alone any sub classes:[img]http://www.stormrage.com/blogpix/SQLProject.png[/img]This is really basic stuff... how can i use a windows.form object, if I cant reference it? the Intellesense fails the moment i try Imports System.W*, so I can't provide a reference.Thu, 01 May 2008 07:09:12 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic442464-386-1.aspxHello all,I'm trying to generate a custom graphic for a reporting services report using a SQL CLR stored procedure. (A RS CRI approach has become stuck at the VS integration phase so am trying to do it in SQL Server instead).I've found I cannot access System.Drawing and most of the .net libraries and add references to them in a SQL Project in Visual Studio. It also does not permit a reference to another project that does hold them because it is not a SQL Project"SQL Server projects can reference only other SQL Server projects.".Does anyone know how to get to the full .net framework (which I thought was sort of the point for having CLR sprocs in the first place)?Thanks gang for your attention!much appreciated.Mon, 14 Jan 2008 08:16:35 GMTomhogehttp://www.sqlservercentral.com/Forums/Topic494616-386-1.aspxI wasn't sure where to post this because it's really a question about internals.What I would like to do is programmatically register a server to management studio.The reasoning: I am in an environment where I manage roughly 60 production instances and we are adding more at a fairly decent rate. We have a database layout where we make it part of our initial setup to add the server to a centralized admin instance/dba database/instance table. What I am interested in trying to do is to be able to read from that table to populate my registered server list.Some of the details I store on the table are: production (yes/no), active (yes/no), functional business group (ie customer service, accounting, payroll, etc.)The ideal way for me to put them in my registered servers list would be something like:Production Customer Service SQL 2000 instance_name_01 SQL 2005 instance_name_19... and so on through the instances. Doing so manually would take time to get that in there, but we already have those details stored on our tables, so that's why I thought there may be someone out there who dabbled into this.I found the Smo function to enumerate all of the registered servers, but that class doesn't allow the addition of new servers.It very well may not be possible, but I wanted to see if anyone else ever had this idea or not?With more and more servers coming online, and with other team members setting them up, I would really like to have a way to dynamically keep my registered server list up to date.One thought I did have was to enumerate through the table to generate a similar XML formed file that the import/export functionality provides such as this: [quote]Group name="Local Machine" description=""> Server name="(local)" description=""> ConnectionInformation> ServerType>8c91a03d-f9b4-46c0-a305-b5dcc79ff907 ServerName>(local) AuthenticationType>0 UserName /> Password /> AdvancedOptions> PACKET_SIZE>4096 CONNECTION_TIMEOUT>15 EXEC_TIMEOUT>0 ENCRYPT_CONNECTION>False AdvancedOptions> ConnectionInformation> Server> Server name="divwl-92ts1d1" description="Local instance - 'divwl-92ts1d1'"> ConnectionInformation> ServerType>8c91a03d-f9b4-46c0-a305-b5dcc79ff907 ServerName>divwl-92ts1d1 AuthenticationType>0 UserName /> Password /> AdvancedOptions /> ConnectionInformation> Server> Group>[/quote]I had to take out the other brackets to get it to show the code. Sorry about that.Any other thoughts/ideas would be helpful :)Thanks to all,SteveFri, 02 May 2008 23:39:13 GMTSKhttp://www.sqlservercentral.com/Forums/Topic490356-386-1.aspxHere is what I am trying to do GOCREATE ASSEMBLY ActiveDCom FROM '\Comms\ADCom\ActiveDirectoryCom.dll'WITH permission_set = unsafegoCREATE Aggregate ADUserFullName (@UserID char(100))Returns char(100)EXTERNAL NAME [ActiveDCom].[ActiveDirectoryCom.ADCom].[UserFullName];GO My dll Has a class ADCom with a function UserFull NameI get this error Incorrect syntax near '.'.I have also tried goCREATE Aggregate ADUserFullName (@UserID char(100))Returns char(100)EXTERNAL NAME [ActiveDCom].[ActiveDirectoryCom.ADCom.UserFullName];GO And I get CREATE AGGREGATE failed because it could not find type 'ActiveDirectoryCom.ADCom.UserFullName' in assembly 'ActiveDirectoryCom'.Any help would be greatly apprecatedThu, 24 Apr 2008 16:38:22 GMTpcplayerhttp://www.sqlservercentral.com/Forums/Topic492471-386-1.aspxI am new to this and I am trying to write a script that deletes a stored procedures and the assembly. First I need to check that the procedures and assemby exist before I delete them. Here is what I have but it doesn't seem to work:use APif assemblyproperty ('GetTop10Vendors', 'SimpleName') is not null begin drop proc GetTop10Vendors drop assembly GetTop10Vendors endany ideas as to what I could be doing wrong?Tue, 29 Apr 2008 14:01:06 GMTComputerStudenthttp://www.sqlservercentral.com/Forums/Topic488103-386-1.aspxI am pretty new to SQLServer and C# so this may be a dumb question. I have created a simple application using C# (Started with the Express Edition 2008 now using VS Standard 2008) that automates a number of steps in upgrading our product on a customer's server. One of these steps involves updating the database schema, changing some data and references, etc. I chose to do this part using the SMO libraries. Everything works fine with SQLServer 2005 but at least one of our customers is still running SQLServer 2000. This customer is getting the following error on the first SQL command being executed (ALTER TABLE acct DROP CONSTRAINT UQ__acct__2D27B809):[font="Courier New"]ExecuteNonQuery failed for Database 'bonuat'. Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)[/font]I have since created a SQLServer 2000 test environment and replicated the issue. I have tried numerous things such as explicitly including the Microsoft.SqlServer.BatchParser.dll in the project and copying out with the build but I cannot get rid of this error.I am beginning to suspect that this error is actually coming from SQLServer which is why adding the DLL to my application didn't help. Am I going to have to rewrite the app using predecessor to SMO (I forget its name)?I would appreciate any assistance you can give me on resolving this.Mon, 21 Apr 2008 10:39:03 GMTRon Smithhttp://www.sqlservercentral.com/Forums/Topic485286-386-1.aspxI'm going thru the Wrox Book "Professional SQL Server 2005 CLR Programming" and am stuck on page 36, uppermost paragraph where the authors mention using a tool to see what the auto-deployment and build processes are doing. I've written a few .NET Select, Updates, etc. but I'm still new to SQL.The tool is: SQL Server 2005 -> Performance Tools -> SQL Profiler.I have downloaded and installed SP 1 for SQL 2005, the Tools Kit and the Advanced Tools. But after all that, I still do not see any mention of Performance tools in my computer.I don't know where else to look or how to proceed. Help and advice anyone!Thanks,Victor VictorTue, 15 Apr 2008 14:00:35 GMTkayucahttp://www.sqlservercentral.com/Forums/Topic476253-386-1.aspxI have a vb.net app connecting to a sql database.When I run the app on the PC running Sql Server it logins ok.When I try and run the app on a different PC, I get the following errorSystem.Data.SqlClient.SqlException: Login failed for user 'BM-PC\Guest'.I am using the following connection stringDim connstring As String = "server=BM-PC;Initial Catalog=EquinexMasterSql;User ID=username;Password=password;Trusted_Connection=False;"It looks like the users, login and database security is configured correctly in Sql Server Management Studio.Any suggestions are most welcome.Fri, 28 Mar 2008 09:58:45 GMTtechsupthttp://www.sqlservercentral.com/Forums/Topic483423-386-1.aspxI'm running 32-buit SE. AWE Enabled and plenty of RAM on the box.A CLR Stored proc started to fail after running OK since release 10 days ago. The proc does some CPU-intensive XML operations. Error:Executed as user: myserver\myProxy. ...cute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 01:30:00 Error: 2008-04-10 01:34:56.20 Code: 0xC002F210 Source: Stored Proc to Execute SQL Task Description: Executing the query "EXEC dbo.myStoredProc ?" failed with the following error: ".NET Framework execution was aborted by escalation policy because of out of memory. System.Threading.ThreadAbortException: Thread was being aborted. System.Threading.ThreadAbortException: at xxxx.yyyyyy.zzzzzzzzz(String v_strName, SqlXml v_objXml, String v_strXsl, Boolean v_bln) ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 01:30:00 Finished: 01:34:56 Elapse. The step failed.This is failing from a SSIS job. If I run the stored proc from SSMS I get similar errors. The -g option seems to cover part of this problem, but I have not seen the specified errors in my SQL logs ("Failed Virtual Allocate Bytes...") - this is described in "Using the SQL Server Startup Options".I was able to re-create (on a test server) by re-starting with -g100.I'm under pressure to get this working. I have just re-started the server and the job ran cleanly. However, will this cure the problem after 10 days ?My questions:1 - has anyone seen this (ie "escalation policy because of out of memory")?2 - has anyone used the -g option at atartup? I'm especially concerned about the impact on other applications on the server.3 - is there a possible memory leak and - if so - how can I identify it?Thu, 10 Apr 2008 23:34:04 GMTian treasurehttp://www.sqlservercentral.com/Forums/Topic484274-386-1.aspxFirst time writting a CLR sproc and need a littel help. I am using the clr to loop thru a record set, I am also relatively new to VB.net.. Anyway, I am opening a recordset and looping thru it and validating certain conditions. One of those conditions is whether the State abbrevation is a valid State... I have a sproc written in T-SQL that does that just fine against my State table. First off let me state that I have no error handling in the CLR sproc. However the debug window in VS is giving this error [quote]A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll[/quote]The State Valid proc returns a 0 or 1...Here is how I am calling it.[code]Dim command As SqlCommand Dim strReturn As String Using conn1 As New SqlConnection("context connection=true") command = New SqlCommand("sproc_ValidateStateAbbr", conn1) command.CommandType = CommandType.StoredProcedure command.Parameters.AddWithValue("@State_Abbr", strDbtrState) strReturn = command.ExecuteNonQuery.ToString '************************************************************* '* If the Return from the proc is 0 then the state is not good '************************************************************* If strReturn = "0" Then blnFailedValidation = True End If End Using[/code]It fails on this line[code]strReturn = command.ExecuteNonQuery.ToString[/code]I really just need to know how to execute another stored procedure from within a CLR stored procedure...Thanks in advance.TimMon, 14 Apr 2008 03:14:48 GMTTim Hansonhttp://www.sqlservercentral.com/Forums/Topic484036-386-1.aspxHi i am using SQL Server 2005.I want to insert Korean characters in a table.NVARCHAR datatype supports unicode characters.Is there is any settings to change in the server? ThanksFri, 11 Apr 2008 23:50:14 GMTsharmahttp://www.sqlservercentral.com/Forums/Topic480330-386-1.aspxIs there any software for source and version control which integrates well with SQL Server that anyone can recommend?Fri, 04 Apr 2008 17:50:44 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic478765-386-1.aspxI have a table that contains notes in separate rows with a column indicating when the note was added.[i]Create table Fred ( account int ,created datetime ,note varchar(max))[/i]The result that I want would have the concatenated [i]created[/i] and [i]note[/i] aggregated into a single row for each account. I can get the single column [i]note[/i] to aggregate correctly, but I need the notes to be ordered by [i]created[/i] descending within the aggregation.I have tried TVF's and subqueries to order the rows before they were aggregated, but no luck there (can't use order by in a subquery or TVF). Also, tried to create an aggregation that takes 2 parameters as an input, didn't like that either.Any ideas?Thanks for your help.Wed, 02 Apr 2008 12:10:31 GMTRichhttp://www.sqlservercentral.com/Forums/Topic474374-386-1.aspxI have the following line:[quote]EXEC master.dbo.xp_cmdshell 'net use x: \\server1\sql$ /user:domain\sqltest passwd /persistent:no'[/quote]I want to replace the [i]xp_cmdshell [/i]call with a call to a CLR sproc, like this:[quote]exec dbo.CLR_usp_RunGenericCmdPromptCommand 'net use x: \\server1\sql$ /user:domain\sqltest passwd /persistent:no'[/quote]Does anyone have code to do this sort of thing?(Any code sample running a command that is ordinarily run from Windows command prompt will do)Tue, 25 Mar 2008 14:10:59 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic477833-386-1.aspxHi All,Following are the schema detailsEMPLOYEE( #EmpId, Ename, Salary, #ManagerId(EmpId), #DeptId)DEPERTMENT (#DeptId, DeptName, #DeptHead(EmpId))SELECT Employee Name, Manager Name, Department name, Department Head Name for all employees, with out using three instances of EMPLOYEE table.can anybody help me out in this. At least tell me is it possible or not.below is the query that i tried.SELECT E1.Ename, E2.Ename AS [MgrName], T.DeptName, T.Ename AS [Dept Head Name]FROM Employee E1INNER JOIN ( SELECT D.DeptId, D.DeptName, E.Ename FROM Department D INNER JOIN Employee E ON E.EmpId = D.DeptHead) AS T ON T.DeptId = E1.DeptId LEFT JOIN Employee E2 ON E2.EmpId = E1.ManagerIdRegardsEnbeeTue, 01 Apr 2008 10:12:03 GMTEnbeehttp://www.sqlservercentral.com/Forums/Topic476785-386-1.aspxHi,I have two software systems as the following:1) Main software, Building on Sql Server 2005.2) Second Software, Building on DBISAM version 4.1On main software (SQL server), I have table including the name of staff, sex, nationality, age ... etc, and I want to transfer any change on this table (insert\update\delete) to same table was built on second software (DBISAM). I mean that I need online transfer.How can I do that?I am new in SQL server and I need your help.RegardsHesham Mon, 31 Mar 2008 00:15:34 GMThisham2778http://www.sqlservercentral.com/Forums/Topic476786-386-1.aspxHi,I have two software systems as the following:1) Main software, Building on Sql Server 2005.2) Second Software, Building on DBISAM version 4.1On main software (SQL server), I have table including the name of staff, sex, nationality, age ... etc, and I want to transfer any change on this table (insert\update\delete) to same table was built on second software (DBISAM). I mean that I need online transfer.How can I do that?I am new in SQL server and I need your help.RegardsHesham Mon, 31 Mar 2008 00:16:05 GMThisham2778http://www.sqlservercentral.com/Forums/Topic475997-386-1.aspxDear all,I want to use C# to write a simple UDF :(1)input a character, then(2)to have a simple check, then(3)might replace it with another character.Here is the source, it could compiled successfully :using System;using System.Collections.Generic;using System.Text;using System.Data;using Microsoft.SqlServer.Server;using System.Data.SqlTypes;public class T1{ public static string common_ReplaceChar(string inputString) { string outputString = ""; if (string.IsNullOrEmpty(inputString)) { return ""; } outputString = inputString.Replace("&", "&"); outputString = outputString.Replace("<", "<"); outputString = outputString.Replace(">", ">"); outputString = outputString.Replace("\"", """); return outputString; }}But I don't knwo why I got this error :CREATE PROCEDURE dbo.CLR_Common_ReplaceCharASEXTERNAL NAME commonreplace.T1.common_ReplaceCharError : Msg 6567, Level 16, State 2, Procedure CLR_Common_ReplaceChar, Line 1CREATE PROCEDURE failed because a CLR Procedure may only be defined on CLR methods that return either SqlInt32, System.Int32, void.Is my C# program still OK ?If no, how should I correct it ?Fri, 28 Mar 2008 05:21:24 GMTonlohttp://www.sqlservercentral.com/Forums/Topic472430-386-1.aspxI have to access a SQL Database using a vb.net (net 2) winforms application. Would you recommend using table adapters or data adapters?ThanksThu, 20 Mar 2008 11:12:28 GMTtechsupthttp://www.sqlservercentral.com/Forums/Topic474409-386-1.aspxGuys ..Im a very very confused Jr DBA/Dev here !I need to know what exactly other languages [b][C#/VB.net/ADO/XML/ASP.net..etc][/b] that I should learn in order for me to be able to excel in SQL Development.I started getting my hands dirty with some SSIS / SSRS & SSAS, but inorder for me to handle situations such as [Find if file exist in ftp before loading, Checking a certain row of a data file before processing/ Zipping and Unzipping data files and etc...] What should I need to know well in order for me to add more value to my skills?I would like to learn 1 language and master it pretty well.What are your suggestions?And what is this CLR that is being talked about ?Does it use other language besides SQL?Do guide this lost JR .......:unsure:Tue, 25 Mar 2008 15:27:52 GMTLookup_BIhttp://www.sqlservercentral.com/Forums/Topic471976-386-1.aspxI have a CLR assembly loaded (UNSAFE) in SQL Server 2005. Assembly is in written C#. It calls functions from another assembly (also successfully created in SQL Server) written in Managed C++ and compiled with /clrure. The later one calls functions from native DLL. When I try to call one of the functions from my C# assembly I get this:Msg 6522, Level 16, State 1, Line 1A .NET Framework error occurred during execution of user-defined routine or aggregate "MyClrFunction": System.TypeInitializationException: The type initializer for ' System.Security.Policy.PolicyException: Execution permission cannot be acquired.System.Security.Policy.PolicyException: at System.Security.SecurityManager.ResolvePolicy(Evidence evidence, PermissionSet reqdPset, PermissionSet optPset, PermissionSet denyPset, PermissionSet& denied, Boolean checkExecutionPermission)at System.Security.SecurityManager.ResolvePolicy(Evidence evidence, PermissionSet reqdPset, PermissionSet optPset, PermissionSet denyPset, PermissionSet& denied, Int32& securitySpecialFlags, Boolean checkExecutionPermission)System.IO.FileLoadException: at .ThrowNestedModuleLoadException(Exception , Exception )at .LanguageSupport.Cleanup(LanguageSupport* , Exception innerException)at .LanguageSupport.Initialize(LanguageSupport* )at .cctor()System.TypeInitializationException: at Functions.MyClrFunction(SqlString key)Calls into C# assembly work perfectly. Calls into C++ assembly produce the above message no matter what I don inside (even if I just return immediately).Any hints what's wrong? Or at least how I can track this down?Wed, 19 Mar 2008 15:49:19 GMTValtDeveloperhttp://www.sqlservercentral.com/Forums/Topic471684-386-1.aspxHas anyone out there tried to use the PayPal Payflow dll as a sql assembly? Currently we are accessing it from our .NET app, but it's in the middle of about 20 sql statements so we'd like to do it all from one main stored procedure. However, I haven't been able to create the assembly. I posted on the PayPal forum, but apparently they haven't done this and don't know the process either; only one of their merchants has done it and this is what that merchant gave them:First you create an Assymetric key for the PayFlowPro.dll. Then you create a login using that key with unsafe access permission.================================================================USE [Master]CREATE ASYMMETRIC KEY keyPayPal FROM EXECUTABLE FILE = 'C:\Program Files\PFLUtilities\Payflow_dotNET.dll' CREATE LOGIN lgnPayPalKeyLogin FROM ASYMMETRIC KEY keyPayPal GRANT UNSAFE ASSEMBLY TO lgnPayPalKeyLogin GO================================================================Then, you create an assembly for that DLL with UNSAFE permission.================================================================USE [PFL]CREATE ASSEMBLY asmPayPal FROM 'C:\Program Files\PFLUtilities\Payflow_dotNET.dll'WITH PERMISSION_SET=UNSAFEGO================================================================And finally, you create an assembly for the DLL you created that has SAFE permissions================================================================USE [PFL]GOCREATE ASSEMBLY asmTransaction FROM 'C:\Program Files\PFLUtilities\PFLAuthorization.dll' WITH PERMISSION_SET=SAFE GO================================================================I can do all of the above steps up until the last create assembly; not sure if I need to have a dll created that calls the Payflow_dotNET.dll? Any & all help is appreciated.Wed, 19 Mar 2008 10:01:33 GMTMaquishttp://www.sqlservercentral.com/Forums/Topic469318-386-1.aspxI am currently trying to set up a SQL Server Agent job that calls a batch script that then calls a perl script which then has to call another batch script. The first batch script works and starts the perl script. The perl script then completes but doesn't start the second batch script half way through using the perl system command. It finishes ok saying it is successful but it hasn't actually started the second batch script. It all works fine from the command prompt.Has anyone got any ideas?Fri, 14 Mar 2008 06:10:57 GMTkeith100159http://www.sqlservercentral.com/Forums/Topic446829-386-1.aspxI have a table "m_table", containing a column "theCount", that is a derived column and contains a UDF (say "myUDF") in its definition.When i do "select thecount from m_table" in SQL Server query browser, result is OK. But, when the same column is selected by a UDF designed in .net CLR, it gives following error.[b]This statement has attempted to access data whose access is restricted by the assembly[/b]the .net code contains following way of connecting to the database[b]using (SqlConnection conn = new SqlConnection("context connection=true"))[/b]I think, that "myUDF" doesnt have sufficient privileges for .net CLR.Any idea ????Thu, 24 Jan 2008 04:25:37 GMTMuhammad Furqanhttp://www.sqlservercentral.com/Forums/Topic452981-386-1.aspxI need to create a sproc/UDF that takes the name of a Windows account as input - - and spits out all Windows security groups that this account is a member of.The opposite of that is given by the well-known xp_logininfo extended sproc:xp_logininfo ' ', 'members';I think the CLR is the best way to access Active-Directory info, so I am leaning towards creating a CLR object.I know the System.DirectoryServices namespace is where the .NET framework methods are found.But not much more! :)Can someone give me some pointers on this?thanks!Thu, 07 Feb 2008 14:50:14 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic467924-386-1.aspxHi, I want to send an Email through Data Driven Subscription...i have created that through reportmanager but its giving the status as [b]"Failure sending mail: The transport failed to connect to the server."[/b]wht might be this?can any one please help me...and one more error am getting always is [b]"The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database[/b]"am saving data source credentials but its giving error like this....how to store data source credentials permanently in the report server database?Wed, 12 Mar 2008 00:59:16 GMTanitha.mannavahttp://www.sqlservercentral.com/Forums/Topic467013-386-1.aspxHi All,I'm new to the CLR arena, but I've noticed some funny activity that I am hoping someone maybe able to explain to me. I've read most of the forum posts I could find on CLR waitstates but couldn't find one that related to my question.I am seeing large numbers of wait states for "CLR_Auto_Event" and "CLR_Manual_Event" on one of 8 SQL Servers. These waits have not been present previously and nothing has changed interms of SQL Settings, databases additions, and we don't run other applications on the SQL Server. I noticed the high count on the "Performance Dashboard" as the second highest waitstate behind SLEEP which dosen't seem to be of issue. Since we are running Dashboard Reports we are at SQL 2005 SP2. I'm sure your all going, OK thats nice check your CLR code that is causing it. But the thing is we don't have CLR code and it is disabled on the server from executing. Here are the details I've collected along the way.1. sp_configure and Surface Area Config shows CLR Integration disabled (0)2. The CLR wait states continue to increment frequently throughout the day, and I've run a profiler trace without any CLR assemblies appearing.3. select * from sys.dm_clr_properties shows:[b]directory C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\version v2.0.50727state CLR is initialized[/b]4. select * from sys.dm_clr_loaded_assemblies shows:[b]no loaded assemblies[/b]5. select * from sys.dm_clr_tasks shows: [b]0x00000001378B8080 0x000000008001C1D8 0x0000000000000000 E_TASK_ATTACHED_TO_CLR E_ABORT_NONE E_TYPE_USER 0 00x00000001672D0380 0x00000001244F01D8 0x0000000000000000 E_TASK_ATTACHED_TO_CLR E_ABORT_NONE E_TYPE_ADUNLOAD 0 00x000000014B55E120 0x000000012113A1D8 0x0000000000000000 E_TASK_ATTACHED_TO_CLR E_ABORT_NONE E_TYPE_FINALIZER 0 0[/b]My question I guess is how can I have waitstates for CLR but the ability to execute it on the server is set to "disabled"Mon, 10 Mar 2008 16:03:37 GMTTrevor McClintockhttp://www.sqlservercentral.com/Forums/Topic466170-386-1.aspxLast week I had created and added a new CLR assembly to a database. I stopped working on it to work on another project, and today I could no longer run the functions from the assembly. I kept getting the following error:Msg 6533, Level 16, State 48, Line 1AppDomain .dbo[ddl].59 was unloaded by escalation policy to ensure the consistency of your application. Out of memory happened while accessing a critical resource. I tried deleting the assembly and was going to try recreating the assembly. Now when I run the Create Assembly statement I get the same error. I tried creating on another database and it did not work. I tried creating an empty assembly just to see if it could have been something with my assembly, but I couldn't even do that.What I don't understand is why last week I was able to create the assembly on the database, and add the functions to the database, and now this week it is broken. Could it have been some update that occurred to our server? Is there any options that I need to check? Any help would be greatly appreciated. Thanks in advance!Fri, 07 Mar 2008 15:08:24 GMTJ Sitz