Run SCCM report or SQL query using sqlcmd

Recently I needed to run an asset inventory on a client’s environment. The inventory was to collect loads of data from the database. Because of the size of the data we are extracting, SCCM report server was crashing with “not enough memory” error.

So I needed another way to run this report (not even using SQL MMS) hence used sqlcmd. 

Using sqlcmd is pretty simple process but I had few issues in the beginning.

More command line options and info here

This will work assuming (at least I have tested);

     - You are running the sqlcmd on the local server where the SQL server is installed

     -  Instance name used as default MSSQLSERVER

 

1. Create a SQL query, copy the query  to notepad then save it as tableview.sql
For this example I have used below query;
Select distinct table_name,COLUMN_NAME From Information_Schema.columns
where table_name LIKE ('v_%')
Order by table_name

2. The above query is for SCCM reports to run in the console.
However when we use the same query we need specify the database name in the query.
Otherwise Query will throw an error similar to table not found.

 So the modified query with SCCM database name will look like this;
USE CM_LAB
GO
Select distinct table_name,COLUMN_NAME From Information_Schema.columns
where table_name LIKE ('v_%')
Order by table_name
Change CM_LAB to your database name

3. Copy the tableview.sql to C:\temp\
4. Launch command prompt and type below command 
sqlcmd –S localhost -i C:\temp\tableview.sql  -o C:\temp\tableview.csv

 Make sure S is uppercase and i and o is lower case.

5. The output file tableview.csv will have data similar to the below;

 Trouble Shooting:
1. When running sqlcmd on the local machine where SQL server is installed and in the command if we use sqlcmd –S SERVERNAME\SQLINSTANCE –i C:\temp\tableview.sql
The command window will produce an error as below;

 HResult 0x57     Level 16     State 1
SQL Server Network Interfaces: Connection string is not valid [87].       
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..      
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

2.When running sqlcmd on the local machine where SQL server is installed and in the command if we use  sqlcmd –S localhost\SQLINSTANCE –i C:\temp\tableview.sql
The command window will give same error as above
3. When running sqlcmd on the local machine where SQL server is installed and in the command if we use  sqlcmd –S <localhost\SQLINSTANCE> –i C:\temp\tableview.sql
                                            Or
sqlcmd –S <localhost> –i C:\temp\tableview.sql
The command window will give error as below;

 The System cannot fine the file specified.
 4. When running sqlcmd on the local machine where SQL server is installed and in the command if we use  sqlcmd –S (localhost\SQLINSTANCE) –i C:\temp\tableview.sql
                                             Or 
sqlcmd –S (localhost) –i C:\temp\tableview.sql
The command window will give error as below;

 HResult 0x35, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.