How Are MS Customers Bridging The Parameter Gap When Calling Sqlclr Sps From External Ado Apps?
Sep 14, 2007
we've seen the ease of calling sqlclr stored procedures whose parameters are known sql data types but are wondering what we're in for when we wish to pass .net objects to a sqlclr stored proc. We're confident that the sp's external assembly itself would build if instructed to "receive" .net objects etc, but can't envision how/if the sp's signature would generate nor can we imagine how such an sp would be called. We're not even sure we can envision how another sqlclr object (eg a trigger) would pass .net objects to the sp. Are we faced with using user defined data types if we need to pass .net objects to such sp's?
View 8 Replies
ADVERTISEMENT
May 5, 2006
I can create an external text file from within the SQLCLR project, but I cannot run an external executable. Just in case you are asking, I need to do this to push data into a legacy application using a different DB format. I have found it best to simply use my old language (Clipper) for data validation, etc. - and especially since I require multiple indices to be open. So, if you could just take my word on this.
The following code:
Process newProcess = new Process();
string path = @"C:TEST.BAT";
newProcess.StartInfo.FileName = path;
newProcess.Start();
Executes without error, but does not actually run the external.
Now, I can have a DOS (Clipper) application poll a directory for text files, but I am trying to get away from all these "mini" data transformation applications. If an exception is caused in the DOS app, a remote user on the other side of the country has no idea it is broke and his data (coming from a SQL Mobile Device) never gets to our legacy database structure.
So, am I out of luck?
View 6 Replies
View Related
Jan 4, 2006
Running SQL Dev Edition on Win2K3 Enterprise Edition. I get the following error.
Msg 6522, Level 16, State 2, Line 2
A .NET Framework error occurred during execution of user defined routine or aggregate 'AddressCorrect':
System.DllNotFoundException: Unable to load DLL 'D:CorrectA.dll': Not enough storage is available to process
this command. (Exception from HRESULT: 0x80070008)
System.DllNotFoundException:
at UserDefinedFunctions.CorrectA(String query, String sentlen, StringBuilder errcode, StringBuilder FirmName, StringBuilder urbanization, StringBuilder Dline1, StringBuilder Dline2, StringBuilder LastLine, StringBuilder Stringaddress, StringBuilder DPC, StringBuilder Checkdigit, StringBuilder cityname, StringBuilder stcode, StringBuilder zip, StringBuilder addon, StringBuilder croute, StringBuilder LACS, StringBuilder LOTsequence, StringBuilder LOTcode, StringBuilder PMB, StringBuilder results, StringBuilder strnum, StringBuilder secname, StringBuilder secnum, StringBuilder countyname, StringBuilder countynum)
at UserDefinedFunctions.AddressCorrect(String inputAddress)
Box has 2GB Ram, with no other processes runing, cant understand why it says out of memory.
Appreciate any insights.
Thanks,
Saptagiri
View 11 Replies
View Related
Apr 20, 2007
I created a CLR function based on following VB code:
Imports Microsoft.SqlServer.Server
Public Partial Class SqlClrVB
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function GetTotalPhysicalMemory() As Integer
GetTotalPhysicalMemory = My.Computer.Info.TotalPhysicalMemory
End Function
End Class
The VB code was complied into a DLL called totalmem.dll and call following TSQL to map it into a SQL function:
create assembly totalmem from '!WORKINGDIR! otalmem.dll'
WITH PERMISSION_SET=UNSAFE
go
create function fnGetTotalMem()
returns int
as external name totalmem.SqlClrVB.GetTotalPhysicalMemory
go
When I call this function, it returned following error:
select dbo.fnGetTotalMem()
Msg 6522, Level 16, State 2, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'fnGetTotalMem':
System.IO.FileNotFoundException: Could not load file or assembly 'System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.
System.IO.FileNotFoundException:
at Microsoft.VisualBasic.MyServices.Internal.ContextValue`1.get_Value()
at My.MyProject.ThreadSafeObjectProvider`1.get_GetInstance()
at SqlClrVB.GetTotalPhysicalMemory()
.
Anyone knows why I'm hitting this error? I didn't reference any System.Web interface why it needs to load System.Web assembly? The same code runs OK if I compile it as a separate VB application out side of SQL Server 2005.
Thanks much,
Zhiqiang
View 2 Replies
View Related
May 19, 2004
Hi,
Is there a stored proc or a function I can call from Query Analyzer to execute another .EXE and return when it's finished?
I'd like something like
SET @RET = sp_CallEXE('c:mypathmyprogram.exe myparameters')
(Except for the name of the function, there should be something that does this right? I mean, DTS does it already so it must be referring to a function or a SP.)
I want an answer to so that I can validate that everything went well (hence the @RET variable)
Thanks,
Skip.
View 1 Replies
View Related
Apr 28, 2006
I'm trying to call an external webservice which use https and certificate. I load certificate into MSIE and then made *.cer file. The problem is that if I compile simple console program which use this certificate - than all is OK. But when I compile simple rutine for SQL server and trying to run it - an exception is issued after this line:
service.ClientCertificates.Add(X509Certificate.CreateFromCertFile(certFile));
Exception is:
Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Do you have any advices?
MiSu
View 1 Replies
View Related
Apr 3, 2008
Can you put is a call withing a Data Flow that will call an External Application and pass a parameter to that application IE say a command line app and then take the output and assign it to back to the flow as a "column" or whatever for that row... IE I want to take a value push it to an external app and then the output from that app I want to insert it into another field for that row in the new table I am moving the data to.
View 4 Replies
View Related
Oct 1, 2005
I am using SQL Server 2000. I have some files with SQL-Statements.The SQL-Serveragent runs jobs which execute the SQL-Files:(e.g. osql /E /n /i \serverd$lager_pool.sql)How can I implement an error handling.If an error occurs, the script stops, and I can't read the variable@errorMy script - table xy doesn´t existSELECT * FROM XYSELECT @@errorSELECT 33The execution stops with an error after the first lineThanks for your help.aaapaul
View 3 Replies
View Related
Oct 6, 2011
I have a list of customers in my Customers table. What I am looking to do is to create QR codes for each customer in Reporting Services. I need to include the customer name, address, postcode, telephone number and contact name.
View 5 Replies
View Related
Aug 25, 2015
I have a SSRS 2012 report which references a custom c# assembly.  This report exists in multiple environments (alpha, beta and  production ) which are each associated with different data sources.
Is there a way for the assembly to determine the datasource used by the calling report so it can also connect to it?
View 2 Replies
View Related
Nov 16, 2006
I want to know why this code does not work in a query. The table 'iDay_INTRAD' exists and is detected if I use a statement exec sp_tables iDay_INTRAD. This statement returns one row. The code below returns no rows. Apparently the query is looking for table '@p1' not the value of this variable. Is it possible to do what I want to do in SQL Server?
DECLARE @p1 varchar (10)
DECLARE @p2 varchar(10)
SET @p1 = 'iDay_'
SET @p2 = 'INTRAD'
SET @p1 = @p1 + @p2
exec sp_tables @p1
Thanks
View 4 Replies
View Related
Apr 8, 2008
create procedure t1 (@var int output)
as
begin
set @var =3
end
declare @var int
execute t1 @var output
print @var
the output is ok
but i need it like
execute t1 @var output i should get value
View 5 Replies
View Related
Aug 24, 2007
Hi,
I need to create a batch process which calls a stored procedure.
Here's the scenario.
I have 3 tables
Theater - TheaterId, TheaterName, Revenues,locationid, stateid
State - StateId, StateName
Location - LocationId, LocationName, StateId
There is a stored procedure spoc_updateTheater that accepts the state and location id and runs a set of sql statements against the theater table. However i want this to run for all the locations in a state one by one. There are some 700 locations in 45 states. How do i pass the location and state id one by one to the stored proc. Can i call this from a commandline or run it as batch process?
vidkshi
View 8 Replies
View Related
Oct 2, 2007
How do I specify a parameter as an output parameter --> OUTPUT paramI am referring to how to do this on line 10 below
1 int GetTheReturnValue=0;2//Code not shown//
9 mySqlCommand.Parameters.Add("@returnParameter", SqlDbType.Int, 10).Value = 0; // How to specify output param?10 GetTheReturnValue=mySqlCommand.ExecuteNonQuery();
View 7 Replies
View Related
Apr 8, 2014
I want to add the result of a Select statement inside a parameter, not too sure how to pull this off, but here it is...
--Declare needed variables
Declare @NoOfApprovals Int
--Get count of approvals
Select @NoOfApprovals =
(
select NoOfApprovalsToClaim
[code]...
View 2 Replies
View Related
Apr 12, 2006
I will be calling a stored procedure in SQL Server from SSIS. The stored procedure inserts records in a table by accepting input parameters. In the process, it also generates an output parameter that it passes as part of the parameters defined inside the stored procedure. The output parameter value acts as the primary key value for the record inserted using the stored procedure.
How can I call this stored procedure in SSIS? This is just one of the n steps as I will be extracting the output parameter generated by this stored procedure for the succeeding steps.
View 4 Replies
View Related
Feb 27, 2008
I want to calculate how many number of people have got loans. In my report, there is one column called customerName. I can use count(CustomerName). But sometimes cutomers name appears twise or thrise. count(customerName) does not give the write nu of customers. How do I write T-SQL expression to get the rite no of customer?
Thanks
View 5 Replies
View Related
Sep 9, 1999
How many "applications" can be loaded onto Microsoft SQL Server 6.5?
Additional Information:
The OS platform is Windows NT Server 4.0 (SP5) on a Compaq ProLiant 1600 (PII 450) with 256Mb RAM
Proposed applications, although this probably will mean little, are Dataview (club membership) Peterborough (payroll) and Positive (HR).
Any assistance would be appreciated.
Thanks
Robert
robert@whiteley.com
View 1 Replies
View Related
Oct 21, 2006
Hey guys, my first post so here goes...
I'm wondering if anyone here knows how to write a program (or download a program) that pretends its a sql server and listens for connections, so that when an app thinks its sending its connection string to a server, its sending it to the locally installed program, so that you can get the UID and password from the connection string.
I'm working at a company where we need to write an extension to the software we use but we don't know the login to use for it. These programs are horribly designed. If you run it with the net card disabled it dumps the connection string to the screen but of course it has the UID and pass starred out. Any thoughts?
View 1 Replies
View Related
Jun 21, 2007
I'm struggling to connect to my SQL server whilst running a dot net application which is located on a network drive.
the program will run perfectly from my c drive, but as soon as i move the program file on to the network, it no longer recognises my windows permissions, and does not connect to the database.
can someone help?
is there some code that i need to add to my dot net code?
View 2 Replies
View Related
Jun 7, 2007
I have SQL Server 2000 with databases on the NetApps filer CIFS share.For example, the datafiles are located at \network shareserversharemicrosoftsqlservermssqldata...Whenever there is a filer failover, some of the databases go in suspectmode. Is there a setting on SQL Server like a time out setting from thenet apps that can be modified so that the databases will wait for awhile before going into the suspect mode?Raziq.*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related
Jan 4, 2007
Help!
Does anyone know where can i find some windows or web applications using ssis for manipulations?
View 4 Replies
View Related
Apr 29, 2008
hi,
I have a person table with a field createdate and personid,
how can I display how many customers existed in the table by date.
eg.
1/4 200
2/4 250
3/4 256
4/4 280
regards,
amie
View 4 Replies
View Related
Sep 26, 2007
I am trying to link my ORDERS table to my CUSTOMERS table.
The Primary key in the CUSTOMERS table is a UNIQUEIDENT.
The foreign key it is linking to is an INT.
When I run a query in ACCESS, it says type mismatch....
Any solutions?
View 1 Replies
View Related
Nov 8, 2007
I€™m trying to group records that are in two collections and assign a Master Id. For example:
Example Base Table:
Collection Number Customer Id
--------------------------- -----------------------
3000001 244517
3000001 244518
3000002 244517
3000002 255519
3000002 244518
3000003 255520
3000004 266660
€¦
€¦
Since Customer Id 244517 is in collection 3000001 and 3000002 I want to group all customers in both collection and assign one master id. For example:
Example Results:
Collection Number Customer Id Master Id
--------------------------- ----------------------- --------------------------
3000001 244517 1
3000001 244518 1
3000002 244517 1
3000002 255519 1
3000002 244518 1
3000003 255520 2
3000004 266660 3
€¦
€¦
I€™m not sure how to perform this, can anyone direct me?
View 5 Replies
View Related
Dec 31, 2007
I deployed a clustering project. As a result, I got 10 clusters. Is there an automatic way to create a table with the customer ID and the cluster # that each customer was assigned to?
Thanks!
Ezes
View 4 Replies
View Related
Jan 22, 2007
I have a table with orders and customer info. I want to retrieve only customers with new orders only. These are the available fields: customer Id, Order Id, Order Date, and Order Subtotal. I need help with the "where" section of the query.
View 1 Replies
View Related
May 18, 2006
In Lesson1 of SSIS Tutorial, section "Running the Wizard to Create the Basic Package", is stated
"Specify the data source. You will use the Excel workbook, Customers.xls, as the data source..."
I have both AdventureWorks databases installed on my box as well as a host of samples but I can't find that file. Any pointers much appreciated.
TIA,
barker
View 12 Replies
View Related
Sep 8, 1999
If a user's pc needs to run 2 different applications (sold by 2 different vendors) and one must be 6.5 and the other must be 7.0, are there any problems with the client machine?
My understanding is that each of the apps comes with the pieces it needs to connect. I'm trying now to reach the tech types at the vendors and ask what kind of custom setup (if any) is needed on a user workstation or if the install does everything. If they come with their own dlls and odbc data sources, might all be well?
Does anyone have apps loaded on a desktop that connects to both a 6.5 and a 7.0?
Thanx,
Dick Scherrer
View 1 Replies
View Related
Dec 8, 2006
Hello,
i am planning to build Pocket Pc App and i would like to know if i can use Sql Mobile as my data store. The data base design is quite complex.
I wont be using any recplication/synchronization in my app since its a standalone app.
pls suggest.
Thanks
Karthik
View 3 Replies
View Related
Nov 9, 2007
most articles I read suggest that apps need to retry upon failure in a mirrored environment, using a different connection string, not worrying a whole lot about waiting for the retry. But I heard somewhere that the original connection string can also contain info about the mirrored db, thus eliminating the need to use two different connection strings, and perhaps not even needing to retry programatically. Which is correct? I also heard that programatic retry could be required anyway because of some bug involving the "first" failure and disappearance of a connection pool. Can someone just provide the 50,000 foot view of what it takes to make an app "mirror aware"?
View 1 Replies
View Related
Oct 4, 2000
Hi,
I'm trying to create a resultset with the top 100 customers for the year (based on this year's sales) and for each of these customers, to return 5 top items and their corresponding sales dollars, as well as percentage of total sales achieved by each item. What I'm struggling with is how to return specifically 5 sales items per customer. If I use a temp table or a correlated subquery, what is the exact syntax to use? Any advice will be appreciated. Thanks all :)
View 1 Replies
View Related
Feb 19, 2013
I have a large table that tells me customers and mailings (~4 million rows). So for each customer, I see what mailing(s) they received. It is at the mailing level so if a customer received 2 mailings, their will be 2 records for that customer number, one for each mailing they received. There are 3 different mailings.I am trying to calculate the count of customers that received all 3, as well as those that received 1&2 only, 1&3 only, and 2&3 only. For example:
CustNo Mailing
Tim 1
Tim 3
Dan 1
Dan 3
Jane 1
Max 1
Max 2
Max 3
Lauren 2
Lauren 3
Results:
All 3 - 1
1&2 only - 0
1&3 only - 2
2&3 only - 1
I have tried doing some funky table joining and EXISTS, but the query runs and runs because of how much data it has to sift through. Here is the code I am using to find Mailing 1&2 only customers, but I am not even sure it is getting me the right numbers. There are 2 different types of customers as well which is why I have custtype in there.
Code:
select a.custtype,count(distinct a.custno)
from myTable a
inner join (select distinct custno from mytable where [mailing]=2) b
on a.custno=b.custno
where a.[mailing]=1
and not exists(select * from myTable c where a.custno=c.custno and c.[mailing]=3)
group by a.custtype
View 14 Replies
View Related