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

Executing An External Process() In SQLCLR Project

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

Error Calling SQLCLR UDF

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

Calling VB Based SQLCLR Function Failed With Error Can't Load System.Web Assembly

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

Calling An External Program

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

Calling External Webservice Using Certificate

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

SSIS Calling An External Application

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

Error Handling When Calling External Sql-Files

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

Reporting Services :: SSRS 2005 - Generate QR Codes For All Customers In Customers Table?

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

Reporting Services :: External Assembly Reference Same DB Data Source As Calling Report?

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

Using Variable Value As Calling Parameter

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

Calling Procedure With Out Parameter

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

Calling A Stored Procedure And Pass Parameter One By One

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

How To Specifiy An Output Parameter When Calling Stored Proc In C#

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

T-SQL (SS2K8) :: Embedding A Variable Into A Parameter When Calling A Stored Procedure

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

Calling A Stored Procedure That Inserts Records And Generates An Output Parameter

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

Nu Of Customers

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

How Many Apps Supported?

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

Tricking Db Apps

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

Connect To SQL From .net Apps

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

SQL Server And Net Apps

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

APPS For SSIS

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

Total Number Of Customers

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

Related Customers To Orders

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

Help Grouping Customers In Table

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

Assign Cluster # To Customers

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

Retrieve Only New Customers With Orders

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

Where Is The Tutorial File: Customers.xls?

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

User Pc Running Apps For Both 6.5 And 7.0

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

SQl Mobile For Standalone Apps?

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

Apps Being Mirror Aware

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

Matching Top 5 Items To Customers In Another Table

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

Counting Customers That Are In Multiple Groups

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







Copyrights 2005-15 www.BigResource.com, All rights reserved