Orsburn (Static Content)

Pardon the confusion with all the freely flung usage of the term SQL coupled with the vaguely descriptive "developer" and "server" terms.  This article describes the requirements for getting Oracle 's SQL Developer client to work with Microsoft 's SQL Server database manager. SQL Developer supports connections to SQL Server, but in spite of Microsoft providing its own JDBC driver , SQL Developer will only work with the jTDS driver.  Download that and point SQL Developer to it via: Tools | Preferences… Database | Third Party JDBC Drivers Point SQL Developer to the jTDS driver to use with SQL Server. If connections are being made from the Windows version and Windows authentication will be necessary, then the ntlmauth.dll library will need to be copied into the JDK directory: Copy from PATH_TO_JTDS\jtds-VERSION\PLATFORM\SSO\ntlmauth.dll Copy to JAVA_JDK_DIRETORY\jre\bin\ntlmauth.dll

DBeaver will allow Microsoft Windows single sign on access when connecting to Microsoft SQL Server using the SQL Server driver (rather than jTDS ).  From the driver properties settings, set the integratedSecurity flag to true . Open the Connection configuration panel and choose the Driver properties section. Set the integratedSecurity flag to true . A subtle, but important step is to not provide username and password credentials to the connection.

Generally, once a stored procedure is created, the details of how it works can be forgotten.  Sometimes however, what a procedure does may need to be researched.  The following query makes an attempt to look-up the definition for a stored procedure in SQL Server . NOTE:   Set the value in the DECLARE line at the top to the procedure name to look for (partial strings are fine). NOTE:   SQL Server has the ability to encrypt the definition for a procedure.  A definition value of NULL likely means such is the case for this procedure. DECLARE @procedureName VARCHAR(MAX) = 'PROCEDURENAME' SELECT      sys.all_objects.name AS storedProcedureName     ,sys.all_sql_modules.definition AS storedProcedureDefinition FROM     sys.all_sql_modules     INNER JOIN sys.all_objects         ON sys.all_sql_modules.object_id = sys.all_objects.object_id WHERE sys.all_objects.name LIKE '%...

SQL Server assigns the term "object" to a number of items.  These could be tables, views, columns, parameters, stored procedures, et cetera.  The following query can be used to identify the type of a SQL Server database object. NOTE:   The type is identified in the "type_desc" column. NOTE:   Set the value in the DECLARE line at the top to the object name to look for (partial strings are fine). DECLARE @search VARCHAR(MAX) = 'OBJECTNAME' SELECT * FROM sys.all_objects WHERE name LIKE '%' + @search + '%'

There may be times when it would be nice to know what stored procedures have parameters with a certain name.  For example, what procedures have a parameter named "username?"  Using SQL Server it's just a matter of querying some of the system tables to find out. NOTE:   Set the value in the DECLARE line at the top to the parameter name to look for (partial strings are fine). DECLARE @parameter VARCHAR(MAX) = 'PARAMETERNAME' SELECT      sys.all_parameters.name AS parameterName     ,sys.all_objects.name AS storedProcedureName FROM     sys.all_objects     INNER JOIN sys.all_parameters         ON sys.all_objects.object_id = sys.all_parameters.object_id WHERE sys.all_parameters.name LIKE '%' + @parameter + '%'

UPDATE:   At one point the tip in this article to set the USENTLV2 flag to true was accurate, but it appears it is no longer necessary, at least as far as DBeaver 's use of the driver is concerned.  If it solves an authentication problem, then great.  The expectation is that, an update in one of the packages that make this sort of connection possible has resolved the issue that at one time made this flag necessary. Connecting to Microsoft SQL Server using jTDS with a username and password that's managed by the database manager is pretty straightforward.  It may become a little more problematic however, when trying to use Windows authentication when connecting from a Windows machine. Do not supply a username and password. Set the USENTLMV2 property to true. NOTE:   Step 1 assumes a connection being made from a Windows machine.  If the connection is being made from something else, user credentials may actually be necessary. If the connect...

Issue The PDO class for PHP throws an error when running queries against SQL Server and returning multiple results sets. Cause Without the benefit of the details around the implementation of SQL Server or the SQLSRV PDO driver, there's no way to know what's causing this issue.  However, the anecdotal evidence points to the the lack of support in the SQLSRV driver for SQL Server's ability to return multiple datasets. One of the errors that has been encountered reported a question mark (?) placeholder in each place where an SQL variable was in the script that was passed to the PDO::prepare() method call.  This was taken to mean that the method was perceiving each SQL variable as a parameter that it should be looking for in the parameters array passed in the PDOStatement::execute() method.  In other words, it was interpreting SQL parameters (variables) as PDO parameters (?). Ultimately, this issue is likely to come down to the use of the table data type or tempo...