Call Dll Function from SQL Stored Procedure Using The Current Connection

Execute a SQL Server stored procedure from a DLL

The error is quite clear:

login_vald()- not all code paths return a value

...means that your function is missing a return statement with an object of type DataSet something like:

public static DataSet  login_vald()
        {
            DBConnect myConnection = new DBConnect();
            SqlCommand comm = new SqlCommand("ph.validate_app_user", myConnection.connection);
            comm.CommandType = CommandType.StoredProcedure;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();

            return ds;   //this is missing
        }

SQL Server stored procedure vs an external dll

Use stored procedures, and write your data access layer which calls them via parameterized commands in a separate dll. Stored procedures are a standard and give you a ton of benefits, parameterized commands give you automatic string safety.

This type of design is basically so standardized and has been for years now that Microsoft has included a framework that constructs it for you in .NET 4.

More or less, both you and this other fellow are right, use sprocs for security, and separate your DAL for security and reusability and lots of reasons

How to call C# function in stored procedure

Yes, it is possible to use .NET in a SQL Server 2005 database. Be aware that the .NET version supported by SQL Server 2005 is 2.0.

Here's a link for an introduction to Making a CLR stored procedure using Visual Studio

loading and calling C# (assembly) from a sql stored proc

Well, if you're going to go down this path, you might skip over using C# as a wrapper for your C++ dll (since it sounds like you're just trying to use the SQL Server CLR integration as a means to call into a C++ routine).

Instead, you could create and register a C++ COM+ Application (example) on the server and invoke this from SQL Server using sp_OACreate and related procedures. FYI: You could also create the COM+ Application in C# if you wanted, but it might be more work to interface with the C++ dll.

I think you will have better luck with this approach because a COM+ Application can be configured to run in a dedicated server process instead of within the SQL Server process.

Maybe your situation dictates otherwise, but as other people have commented, it's best to leave this kind of "integration" outside of the database and put it into a separate application layer.

How to return a value from a stored procedures in a large global CORP SQL Server 13.xxx from MS Access 2007-2016 with VBA

  Dim strFrom       As String
  Dim strTo         As String
  Dim SumResult     As Currency
  
  strFrom = "'2021-01-22 05:00:00'"
  strTo = "'2021-01-22 13:15:00'"
  
  With CurrentDb.QueryDefs("qryPT")
     .SQL = "EXEC MyGetSum" & strFrom & "," & strTo
     SumResult = .OpenRecordset()(0)
  End With

Now of course the above assumes that you have a stored procedure saved as MyGetSum.

It would look like this:

CREATE PROCEDURE MyGetSum
   @FROM DATETIME2,
   @TO DATETIME2
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @dt AS TABLE([Timestamp] DATETIME, [Value] INT, [Format] INT, [EquipmentCounterID] NVARCHAR(255)) 

   INSERT INTO @dt 
   EXEC Biop_ProductionCountersInUTC '62','OutputGood', @FROM , @TO 

   SELECT 
   SUM([Value]) AS 'OUTPUT' 
   FROM @dt
END

Now, of course it is VERY possible that you do NOT have a saved stored procedure server side, and for some reason (like world poverty), you have to have all of the code, lines that you have as per above?

Ok, then I would create Two pt queries in Access. The first one is our base t-sql + sql code. You grab that, set the values, and then run it in the 2nd pt query. We again do this, since writing all that in-line t-sql in VBA is a real mess (trying to save world poverty here).

So, I would create the first PT query in Access. You can (have to) type in free form text into the query (but in MS-ACCESS SQL view mode).
So you have this:

DECLARE @FROM AS DATETIME2 
DECLARE @TO AS DATETIME2 

SET @FROM = '@@FROM'
SET @TO = '@@TO'

DECLARE @dt AS TABLE([Timestamp] DATETIME, [Value] INT, [Format] INT, [EquipmentCounterID] NVARCHAR(255)) 

INSERT INTO @dt 
EXEC Biop_ProductionCountersInUTC '62','OutputGood', @FROM , @TO 

SELECT 
SUM([Value]) AS 'OUTPUT' 
FROM @dt

So we place the above in our PT query. We can "think of" this query as our source query.

So, it will look like this in MS-access:

Ok, we save the above as ptSumS (we tag a "s" on the end for source).

In fact the above does not really have to be a PT query, but that will prevent the access query editor from messing with the sql text as we have above.

Now, create the 2nd PT query (that is the query we will actually run).

So, now our code looks like this:

  Dim strFrom       As String
  Dim strTo         As String
  
  Dim SumResult     As Currency
  Dim strSQL        As String
  
  strSQL = CurrentDb.QueryDefs("ptSumS").SQL
  
  Debug.Print strSQL
  
  
  strFrom = "2021-01-22 05:00:00"
  strTo = "2021-01-22 13:15:00"
  
  strSQL = Replace(strSQL, "@@FROM", strFrom)
  strSQL = Replace(strSQL, "@@TO", strTo)
  
  
  With CurrentDb.QueryDefs("qrySum")
     .SQL = strSQL
     SumResult = .OpenRecordset()(0)
  End With

So, either way, using a PT query is the way to go.

The first example - calls a existing stored procedure (so if you have the ability to write + create + save that server side stored procedure, then you can call + use + consume it with the first example.

However, if you don't have the ability to save + create a stored procedure, then we use the 2nd approach. I created the T-SQL as a pt query, since trying to write all that garbage in the VBA editor is too much pain - just type in the t-sql you have into that "handy dandy" pt query. We never run that first PT query - but are using it as a place holder for the t-sql code. It also means we can modify that t-sql - and not have to change the VBA code.

So, we replace the two date parameters , and then run the 2nd example. And by using pt-queries, then we eliminate all the need for connection strings in code (again - really messy in code if try and spew connection string code all over the place).

And while I shoved the results into the currency VBA type, we could for example do this:

  With CurrentDb.QueryDefs("qrySum")
     .SQL = strSQL
  End With

Now RIGHT after above - since we setup the t-sql, we can now run a report, or even a simple access query that is BASED on the above pt "qrySum". From Access point of view, that query will work just like ANY other plane jane query. So right after above, we could launch a report based on qrySum
eg:

docmd.OpenReport "rptMyReport",acViewPreview.

Or we could shove the results into a record set like this:

  Dim rst        As DAO.Recordset
  
  With CurrentDb.QueryDefs("qrySum")
     .SQL = strSQL
     Set rst = .OpenRecordset()
  End With

  debug.print "sum result = " & rst(0)

So you could build up the strSQL in code, and still use the above final code snip, but might as well just type in the whole sql mess into a query, execute a replace on the two values and then shove the resulting sql into that 2nd PT query. We don't modify the first one, since if we "replace" the two date parameters, then after that, how can we know what the last values were - and thus can't use the source query over and over.

However, REGARDLESS, you MUST ADOPT the native 17 or later drivers, since WHEN sql server is using datetime2 (as opposed to datetime), then the standard legacy odbc drivers return datetime2 values as strings - and that is a mess of epic proportions. Same goes for reports and just general use of the data in Access - you REALLY have to ensure that you using the newer native 17 odbc drivers. Or I suppose you can modify the server side table(s) and change the datetime2 columns types to datetime (but that in near all cases is not a practical suggestion as it would mess with existing server side code and queries).

Edit:
Setting up the connection from/inside of Access.

So we have two pass-though queries in Access. The FIRST query as noted is just a place holder for the t-ssql code. We don't need (nor even want) a conneciton string.
To create the first PT query:

It will now prompt for a table - close that dialog.

So, now click on this:

Now change the view to SQL view - with this:

Ok, so now we can paste in our first (source t-sql). That will give us the original screen shot I have in above.

so we have this:

Save the above - we calling it qrySumS

Now, close that, and create our 2nd query.

Again, PT query, but this time do this:

So we display the property sheet. This is WHERE we will setup the conneciton string. (as noted, we could do/have/set the connection string in code - but hey, the UI is less work.

So in the property sheet, click on the connection builder:

This will launch the ODBC connector dialogs - and that is WHERE you can setup + enter the same previous information you shared.

Select new, and then select the ODBC driver.

AS WARNED - try and select a native 17 driver.

You CAN try using "SQL Server" from that list (if you don't have native 17 installed). But that can cause issues with datetime2. "sql server" as a odbc driver is built into ALL copies of windows. If you choose native 17, then you have to install the native 17 driver on all workstations that use the access application.

Ok, so work though the dialogs.

In your case, the server name + sql instance looks to be:

SQLDKBA185

and then user + password

And DO NOT skip the database selection - often so many do!!!

This one:

When all is said and done, you wind up back to the original starting point, but with the name of the connection you typed in

And along the way, there is a "test connection" - do try it.

so, you COULD type in, or even cut+paste in the connection string, but the above connection builder (gee, I use it 10 times a day), should result in this:

Now, this 2nd query can be left blank, since our VBA code fills it out (copies from the first query - replaces the params. We save and called this qrySum in our example code.

So you can type/paste in the connection string. and you can of course use VBA code, but it makes the most sense to just use the connection builder. Note the syntax is VERY similar to the .net connection - but is often a wee bit different - so that's why I suggested to use the connection builder. (we using ODBC as opposed to .net sql provider - but you can often look at one, and translate - re-type to the format required by Access (ODBC format).

Edit #2
If you can determine that the datetime columns are NOT datetime2, then change the t-sql code to use datetime, and you CAN USE the "sql driver" and thus not have to adopt + install native 17 (or later) odbc drivers on each workstation that is to use this application. So, if you use "sql server", then this should/could work on all workstations WITHOUT having to install the later native 17 drivers. This however IS REQUIRED if the table(s) in question on the server ARE using datetime2 as opposed to datetime columns. Do NOT ignore this advice here - much pain and suffering will result if you don't address the datetime2 vs datetime issue.

Calling an API from SQL Server stored procedure

Please see a link for more details.

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);

Code Snippet
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
                 'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT', --Your Web Service Url (invoked)
                 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

Select @ResponseText

Exec sp_OADestroy @Object

---

Related Topics