I require Api call or any ways to find the installed path of SQL SERVER 2005.
This is required becauses you application register set of extended procedures. As part of SQL SERVER 2005 we found that the dll has to be registered with full path in general
<b> "c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn" </b>
since customers may install the server in any drive , i require to find the installed path.
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd (@buildd nvarchar(4), @avdate as nvarchar(25)) Returns nvarchar(25) as Begin declare @ret nvarchar(25) declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from ( select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
I`m developing a web site with many grids, these grids obtains the data from a SQL Server 2005 DB which data is stored in XML Format... many times when the page load is done, this message appears :
File or assembly name Microsoft.Data.SqlXml, or one of its dependencies, was not found.
But when I access a second time to the same page, the data is displayed normally..
Can anyone tell me is it possible to register and access .NET dlls from Stored procedures and how to call those methods in those dlls from Stored procedures
Is there a way to use a custom component programmatically (I'm using C# to create and run my packages) without installing a compiled .dll of the class into the SQL folders and the GAC? For instance, can I instantiate my custom component and somehow pass it to be used in a task in my code.
I'm just wondering what the effect of including a form in a dll would have on a system? I created a dll that actually has a hidden form in it and is called from a stored procedure using SQL Server 2000. I needed some of the functionality of the richTextBox control, only I didn't want to actually display the form with the box on it. This seems to work when called from a stored procedure (the method called returns correct values and the form never shows up on the server), but are there any inherent problems with this I'm not aware of? I wouldn't think it would be a memory leak and we're not too worried about security. I'm using this as a shortcut around having to write something that converts rich text into plain text and allows us to search the document for keywords. I know this might be a ghetto way around it, but it seems to work.
I am developing a Script Task for my SSIS package to transfer a file via FTP over SSH (SFTP). I have a nice set of Assemblies/DLLs (purchased from IP*Works) that gives me the added functionality of SSH.
To even get Visual Studio for Applications to see it in the reference list, I copied the DLL (nsoftware.IPWorksSSH.Sftp.dll) to the C:WINDOWSMicrosoft.NETFrameworkv2.0.50727 directory, added it in the references for the script, and added the Imports nsoftware.IPWorksSSH.Sftp line in the script.
As described in: http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/07/16018.aspx
Yet, when I try to run the SSIS package I get a DTS Script Task Runtime Error:
Could not load file or assembly 'nsoftware.IPWorksSSH.Sftp, Version=7.1.2203.0, Culture=neutral, PublicKeyToken=cdc168f89cffe9cf' or one of its dependencies. The system cannot find the file specified.
I tried to move the DLL to the bin directory in the SSIS Package, but that did not help.
I am developing a native C++ application using SQLCE (.NET is not a current option). Am having a problem while using multiple accessors for multiple blobs in a table. After I read the data for the first blob & try to read the data for the second one, I get an error in one of the dlls (something about using heap data that was freed). Where can I get symbol data so that I can use something like Windbg to resolve my problem.
I've been having an issue with the integration of a third-party DLL into a custom data flow component.
The company sent me a C# project that generates a simple console application. The project includes a class that calls their DLL with DllImport. The console application runs fine.
I created a stand-alone class using the C# class they sent to expose the methods of their DLL. In my custom component, I'm referencing this class to pass data to and from their DLL.
The first method from that stand-alone class that my component encounters simply gets their installation path from the registry and does not use DllImport. That path retrieval works fine. The next method calls a function that is declared with DllImport. Each time the call fails with "System.DllNotFoundException = {"Unable to load DLL AMZip.dll': Exception from HRESULT: 0xE06D7363"}".
I've copied this DLL to countless locations (e.g., the PipelineComponents directory, the project/solution bin directory) and included these paths in all manner of path variables.
What am I missing here? Their DLL is not strong named (does this matter since I'm using DllImport?), my stand-alone class is, and of course, the custom component itself is. I appreciate the help.
Hello, Kind of a .Net question, although specifically related to VSA.
We have a script task that references an external DLL. That DLL is a proxy for a web service and it takes its configuration info (e.g. location of the web service) from a configuration file.
We have created a quick-and dirty test harness exe to consume data using the proxy and it works fine. All you need to do is dump the proxy, the config file and the test harness exe into a folder.
This doesn't work so well when you substitute SSIS for the test harness. We are trying to use a script task which references the proxy DLL to get a response from the web service but when we execute we get:
"Cannot find default endpoint element that references contract 'blah blah blah' in the ServiceModel client configuration section. This might be because no configuration file was found for your application..." [See a screenshot here: http://cid-550f681dad532637.skydrive.live.com/self.aspx/Public/Misc/ssis_script_task_config_error.JPG]
This makes sense of course, right? The DLL needs the config file. However, where do you put the config file so that the DLL (which, remember, is running inside SSIS) can find it? We've tried putting it in the same folder as the .dtsx file, in the same folder as dtexec.exe...nothing works.
To try and debug we put the following code into the script task:
Code Snippet Dim rst_Path As String rst_Path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName.CodeBase) MsgBox(rst_Path)
and the resultant message box was contained "file:C:WINDOWSassemblyGAC_MSILMicrosoft_VsaVb8.0.0.0__<GUID>"
which makes complete sense of course.
So, my question is, how can we configure this whole kaboodle of "stuff" so that it works?
i'm calling a create table SQL from my ASP. There are a lot of fields created by this sql. Wondering if an extended stored procedure would be a better choice? (can't use a stored procedure as i need to pass arrays) Will it enhance performance and also, can i use VB to make that DLL or i need to use VC?
I created an extended procedure DLL according to the guidelines set forth in the Books Online. I placed this DLL to be called in a trigger. The DLL fires, but some aspects of the dll fail. Is there a C lang. limitation when used with SQL server? I can do file i/o in the dll (open a file, write to it) but other functions such as C API functions FindWindow / SendMessage always fail. I have tested the DLL outside of SQL server and it works fine. Whats up, I can't beleive that Microsoft would write SQLServer where only certain functions of the DLL called by SQL server will work.. Any ideas would be appreciated! KT
I'm attempting to copy over extended procedures from another database, and it's only copying stored when I use the "Transfer Database" wizard. Anyone know why it doesn't copy extended, and how I can get it to? Thanks!
Plz help me to know whats wrong with following script:
EXEC sp_addextendedproperty @name = 'col_Programs_ID_Description', @Value = 'The Unique Identifier for a specific Program', @level1Type = 'Programs', @level2Type = 'ID'
I get the following error: Server: Msg 15600, Level 15, State 1, Procedure sp_addextendedproperty, Line 42 An invalid parameter or option was specified for procedure 'sp_addextendedproperty'.
NOTE: If I dont use the last 2 parameters this procedure runs fine, but it adds the Extended Property to the Database Level while I'm trying to add it to the TABLE-->COLUMN level.
I have to write a stored procedure to gather .dat files from multiple network shares to a single location and bulk insert them using format files. I have the SP working that accepts the filename and performs the bulk insert into my SQL tables using the appropriate format file.
Now, I had originally wrote the "copy files" portion in VB using the FSO, but was told it has to all be in a SQL SP. I have been investigating the xp_cmdshell extended procedure, which would work if I weren't pulling the .dat files from a Novell volume. For example connecting to a windows share this works...
exec xp_cmdshell 'dir servershare', no_output
but not when using a servervolume (Novell), even though from the command prompt this command will work to the novell volume.
Does anyone know of any other extended procedure or method in T-SQL that can perform file system functions?
I would like to know if I can determine the calling user from withinan extended stored procedure. I assume it's accessible in theSRV_PROC structure somewhere.Also, does anyone know of a comprehensive list of what is included inthe SRV_PROC structure?This is for SQL Server 2000.Thanks
Does anyone know of a good primer on extended properties and why I would want to use them. I have you the on-line books sections but that does a clear "why". I have search for white papers and similar documents.
I understand they are good for documentation, but I was looking for something that explain Microsoft long-term vision. How I might use them in template scripts, VSTS, etc.
What is the procedure to create a extended stored procedure using C#.
Actually we have created a dll file using c# and it was added to the bin folder of mssql server. Also dll file is added to the sql server using "xp_extendedproc"
But while executing the extended proc we got this error "ODBC: Msg 0, Level 16, State 1 Cannot load the DLL xp_TestSql.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.)"
Could someone who has done it before be kind enough to post a short example of how to make a call from an extended stored procedure to a .NET DLL? Or even direct me to an example, or tell me that this is possible / not possible, it would help. Thanks,Brian
I'm trying to execute an exetended stored procedure from a trigger. The scenario is this: when an input occurs on a table, my trigger is fired, the extended stopred procedure is to be run, and VB app is to be notified that there is new input to process.
Problem is, I can't seem to get the XP to run. I have written a minimal DLL with VC++ 6, has nothing but the required __GetXpVersion function and the trigger function, as:
Pretty simple. I have defined the XP in the master database, set the properties point to the DLL. Believe everything is set up properly. However, when an input occurs, a run-time error is generated on the XP. Attempting to execute it in Query Analyzer gives this error:
"Cannot find the function xp_InputAlarm in the library D:CustomMedInstaCert SourceInputAlarmInputAlaInputAlarm.dll. Reason: 127(The specified procedure could not be found.)."
Which I think means it can't find the proper entry point (xp_InputAlarm) in the DLL. Any help would be greatly appreciated.
Is there any way I can create an SQL script on any extended stored procedures? I need to see what they do. I looked in books online, but it didn't help. Thanks.
I am working with Visual Basic and SQL Server 2000. I am looking to convert some of the VB into extended stored procedures, yet everything I've been reading has said that xp's can only be written in C or C++. Is this true??
is it possible to view the source of an extended stored procedure. I know that it is a dll program but i want to know how works one of my extented stored proc
I'm trying to call XP_CMDSHELL from a stored procedure that's not in the Master db. How do I call XP_CMDSHELL? I've tried "Use Master" but I get an error telling me that I can't use "USE" inside of a stored procedure.
During a Wise Installation upgrade of our software, we are renaming a directory that contains our ExtendedSP DLL. We issue a DBCC TSWSQLXP(Free) call before doing the rename, yet SQL Server, still "holds on" to that DLL. We install a new version, and the ListDLLs utility (from sysinternals) lists the new DLL in the correct directory.
However, when trying to remove the renamed directory, it won't let us remove the old DLL because it says it is in use. We can delete the NEW DLL in the NEW directory with no problem.
I have run the DBCC call numerous times and SQL Server STILL won't release the DLL for deletion. The only way to delete the OLD DLL is to stop the SQL Server, delete the DLL, and then restart SQL Server. We do NOT want to do this because there may be other processes running in SQL Server.
Is there an easy way to perform a SELECT where you haveblah LIKE 'asdf'but instead of just returning all the asdf's, it also looks for àsdf,ásdf, âsdf, etc?
Hello All, i having one DLL which return Keywords. i want to use that DLL in SQL Server Stored Proc. as i know it is possible through Extended Stored Proc. how to create that ? how i can pass Text Stream as input to that DLL in Extended Stored Procedure.
