Tag: Microsoft SQL Server | Page 2

How to Find All References to an Object in a SQL Server Database

You can use the following scripts to find all affected objects that make any references to your search term.

You can either use sql_modules or syscomments to accomplish this purpose. Generally, they should give you the same results, but from time to time, I found that they may give you slightly different results and I haven’t had the chance to look closer how big the difference is. Just to keep in mind.

However, Microsoft has put a warning not to use syscomments in new development work as the feature will be removed in a future version of Microsoft SQL Server.

Using sql_modules:

SELECT DISTINCT OBJECT_NAME(m.object_id) AS "object name", m.*
FROM sys.sql_modules m
WHERE m.definition LIKE '%SearchTerm%'

Using syscomments:

SELECT DISTINCT OBJECT_NAME(c.id) AS "object name", c.*
FROM sys.syscomments c
WHERE c.text LIKE '%SearchTerm%'

sys.sql_modules

Returns a row for each object that is an SQL language-defined module in SQL Server, including natively compiled scalar user-defined function. Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. Stand-alone defaults, objects of type D, also have an SQL module definition in this view. For a description of these types, see the type column in the sys.objects catalog view.

sys.syscomments

Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.
Important
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use sys.sql_modules instead. For more information, see sys.sql_modules (Transact-SQL).

Connect New SSRS Server to Old SQL Server with TLS

This was posted just to share an experience of tackling a problem we encountered when we needed to connect an SSRS Server 2016 to an older SQL Server database (Windows Server 2008 R2) and what steps we took to troubleshoot the issue.

After all the reports were migrated to the new SSRS server, upon creating a new Data Source, I got an error message something along the line to enter the user id and password correctly.

A simple way to check the connection to the SQL Server database is by setting up an ODBC connection to test.

Sure enough, even the ODBC Data Source Test failed with this message:

Microsoft SQL Server Login

Connection failed:
SQL State: '01000'
SQL Server Error: 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(SECCreateCredentials()).
Connection failed:
SQLState: '08001'
SQL Server Error: 18
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error

For the first issue, the resolution was because of the difference of TLS version enabled by default for each server. We know that TLS 1.2 is enabled by default on Windows Server 2016 or later. However, for the Windows 2008 R2 was not set up correctly.

Windows Server 2008 registry with no TLS enabled

No TLS enabled at all!

After following the steps to enable TLS 1.2 on Windows Server 2008, we retried the ODBC connection from the new SSRS server (Windows Server 2016) and it was successful.

With the ODBC connection working, we went back to check the connection from the SSRS itself (the same Windows 2016 Server). But when we clicked on Test connection, we still encountered the following error:

Couldn't connect

The report server couldn't connect to the data source using the information you entered. Make sure you've entered the connection string and any credentials correctly.

Hide error details ^

  A connection was successfully established with the server, but then an error occured during the login process. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)

Logically, both servers should be able to communicate with each other using TLS 1.2 since both were enabled, but this could be caused by the weak cipher on the old Windows 2008. Since we were pressed on time and the client agreed with the temporary risk, the solution that we opted was to enable both TLS 1.0 and TLS 1.1 on both servers in addition to TLS 1.2.

Had these servers reside on the DMZ, we won’t recommend this solution at all. Even for internal servers such as our clients, we don’t recommend this solution as a long-term fix. But in this case, it was good enough.

This was remediated soon after when the client upgraded the database server to a Windows Server 2019, where TLS 1.2 is also enabled by default with strong ciphers being available.

Hopefully, it will help others who are also in the intermediary state of upgrading and trying to troubleshoot a similar issue.

With that, I welcome any input for a better solution (i.e., you have time to test that it only requires TLS 1.1 but not 1.0, or something more secure for that matter).

How to Use Python to Connect to SQL Server

Can Python work with SQL Server database? Some may argue that SQL Server is not the best SQL database for Python, but in most cases it actually does not matter. In my corporate experience, SQL Server may be the easiest database to use with Python. Besides, in the workplace often times we just have to use what’s available to solve our business problems.

One of the advantages of Python language is the short development time that makes it very suitable to use as a simple tool to query a database.

The easiest way to access a SQL Server database with Python is by using pyodbc. From its project description, pyodbc is an open source Python module that makes accessing ODBC databases simple.

There is another driver called pymssql, but I have not tested it and it looks like even Microsoft themselves recommend pyodbc over pymssql although try to remain somewhat neutral.

There are several python SQL drivers available. However, Microsoft places its testing efforts and its confidence in pyodbc driver.

Source: Python SQL Driver: Getting Started.

You can install it by running the pip command in the Command Prompt (Windows) or Terminal (macOS):

pip install pyodbc

Once installed, create a new python file (e.g., hrquery.py) and import the pyodbc.

Example

hrquery.py

#!python3
# hr.py - Look for HR record based on last name.
#         If no argument is passed, return total count of records.

import sys, pyodbc

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=DB_SERVER_NAME;'
                      'Database=DATABASE_NAME;'
                      'UID=SQLID_OR_NTID;'
                      'PWD=PASSWORD;'
                      'Trusted_Connection=no;')

cursor = conn.cursor()

# Check if there is a passed argument (last name)
# If yes, search by last name, in parameterized query
# otherwise, give total count of records from HR table.

if len(sys.argv) > 1:
    lastName = sys.argv[1]

    cursor.execute('SELECT * FROM dbo.HR WHERE [Last name] = ?', lastName)
    for i in cursor:
        print(i)
else:
    totalCount = cursor.execute('SELECT COUNT(*) FROM dbo.').fetchval()
    print(f"\nTotal count in HR table: {totalCount} records\n")

Other than Driver and Trusted_Connection, you need to substitute the values of all the parameters with your own (Server, Database, UID, and PWD).

To run your program, you can launch Command Prompt or Terminal and type the following:

C:\> python.exe hrquery.py lastname

C:\> python.exe hrquery.py

Using Windows Authentication

If you use your NTID or a Windows Service Account (Windows Authentication) to login to the database, then there’s a slight difference in how you setup the connection string. This is the preferred way nowadays since you don’t store your password as a clear text in your code.

You just need to remove the PWD and set the Trusted_Connection to yes so it will look like the following:

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=DB_SERVER_NAME;'
                      'Database=DATABASE_NAME;'
                      'UID=NTID_OR_SVCACCT;'
                      'Trusted_Connection=yes;')

That’s all there is in using Python to connect to SQL Server and the program will work the same way.

How to Get Table Definition in SQL Server

Whenever you need to pull a quick definition of a table in SQL Server. Here’s the SQL command (T-SQL) to get the table definition in a database:

USE [MyDB]
GO

SELECT COLUMN_NAME,
  CASE
    WHEN ISNULL(NUMERIC_PRECISION, 200) <> 200 THEN DATA_TYPE + '('
         + CAST(NUMERIC_PRECISION AS VARCHAR(5))
         + ',' + CAST(NUMERIC_SCALE AS VARCHAR(5))
         + ')'
    WHEN ISNULL(CHARACTER_MAXIMUM_LENGTH, 0) = 0 THEN DATA_TYPE
  ELSE
    DATA_TYPE + '('
      + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))
      + ')'
  END AS DATA_TYPE,
  IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '[MyTable]'
GO

The result would be something similar to this:

COLUMN_NAME	DATA_TYPE	IS_NULLABLE
AccountNo	int(10,0)	YES
AccountName	varchar(50)	YES
Balance	money(10,2)	YES
UpdateDate	datetime	NO

You need to substitute [MyDB] and [MyTable] to the database name and table name that you’re querying. That’s all there is to it.

How to Show Field Values as Columns in SQL Server

One of the most asked questions by users when they pull data using T-SQL is whether or not there’s a way to present the values of a field they’re interested in as columns instead. The answer is of course it’s “Yes.” You can achieve this by using PIVOT relational operator if you use MSSQL 2005 or later.

Without PIVOT

USE [MyDB];

--WITHOUT PIVOT
SELECT CostCenter, [Type], COUNT(AssetTag) AS Total FROM tblInventory
WHERE [Type] = 'Desktop'
GROUP BY CostCenter, [Type]
UNION
SELECT CostCenter, [Type], COUNT(AssetTag) AS Total FROM tblInventory
WHERE [Type] = 'Laptop'
GROUP BY CostCenter, [Type]
ORDER BY CostCenter;

With PIVOT

USE [MyDB];

--WITH PIVOT
SELECT CostCenter, Desktop, Laptop FROM (
SELECT CostCenter, [Type], COUNT(AssetTag) AS Total FROM tblInventory
WHERE [Type] = 'Desktop'
GROUP BY CostCenter, [Type] 
UNION
SELECT CostCenter, [Type], COUNT(AssetTag) AS Total FROM tblInventory
WHERE [Type] = 'Laptop'
GROUP BY CostCenter, [Type] 
) t1
PIVOT
(SUM(Total) FOR [Type] in ([Desktop], [Laptop])) t2
ORDER BY CostCenter;

How to Find All References to an Object in a SQL Server Database

sys.sql_modules

sys.syscomments

Further Reading

Connect New SSRS Server to Old SQL Server with TLS

Further Reading

How to Use Python to Connect to SQL Server

Example

Using Windows Authentication

Further Reading

How to Get Table Definition in SQL Server

Further Reading

How to Show Field Values as Columns in SQL Server

Without PIVOT

With PIVOT

Further Reading

Recent Posts

sys.sql_modules

sys.syscomments

Further Reading

Further Reading

Example

Using Windows Authentication

Further Reading

Further Reading

Without PIVOT

With PIVOT

Further Reading

Recent Posts

Tags