Question of the month October 2016 – What is the result of $+$?

When you execute this code

SELECT $

the result you get is 0.00

Now the question is what is the result of the following code? Choose the correct answer

SELECT $+$

Answers

(1) 0.00

(2) Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '$'.

(3) Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '$+$'.

(4) Msg 126, Level 15, State 1, Line 3
Invalid pseudocolumn "$+$".
Advertisements

Two methods to solve Database Suspect error

This is the Guest post by my friend and SQL Server DBA Daniel Jones

Elimination of SQL Server Database Suspect Error 926

Microsoft SQL server comprises of multiple tempting features within it. These characteristics make the server one of the most reliable database application in market. It is widely considered as a back-end for saving data of Windows application. However, while accessing SQL server users may experience variety of errors and thus leading to interruption in work. These errors may get resolved either from some server commands or from the server management studio. If required, then third party utility may also be needed for troubleshooting errors. In this blog, we are going to discuss about SQL server error 926 and measures to fix it.

Problem Description

When a user is operating SQL server for managing his database, he/she might face some issues while mounting the database. The database may be inaccessible and thus, you may not be able to mount it. Well, such type of scenario leads to occurrence of error code 926 in SQL Server.

error-926-1

Well, there are some other scenarios when such error occurs and they are highlighted below:

  • When the server user is attaching database
  • While restoring database in the SQL server
  • Extracting transactions from database log

Why SQL Server Error 926 Occurs?

There is one or the another reason behind occurrence of each server error. Similar is the case with this 926 error. The most common reason behind this error is the corruption in MDF file of the server. Besides this, there are some other factors also that are responsible for such error in one or the other way:

  • The database might be marked as suspect
  • Corruption in header part of the system file
  • Some malware defect in hardware of machine
  • Due to sudden shut down of the server application

Tip: If this error is not resolved immediately then, this may result in loss of your important data. Therefore, it is strongly recommended to server users that instead of ignoring such error they should resolve them as soon as possible.

Solutions To Fix Microsoft SQL Server Error 926

A server user can himself rectify the error code 926 of the database with help of any one solution, which are discussed below:

Solution1: Fix Error 926 with Server Management Studio

Follow these steps for fixing error with inbuilt features/options of the management studio:

  1. Launch SQL Server Management Studio on your computer system

error-926-2.png

  1. Go to Object Explorer >> Server Instance and choose Stop option

error-926-3

  1. Now minimize the management studio and open Control Panel on computer
  2. In panel screen, change the view screen to Large icons and click on Administrative Tools for displaying further list

error-926-4.png

  1. From the list of tools, select Services option for proceeding further

error-926-5.png

  1. Search for SQL Server (MSSQLSERVER), right-click on it and choose Stop option from the menu

error-926-6.png

  1. Close the control panel window and launch My Computer or Computer on Windows. In computer, go to the following location:

error-926-7.png

  1. Select MSDBDATA and MSDBlog files and move them to some other place

Note: You are supposed to move these files not copy them to another location

error-926-8.png

  1. Go to location where you had moved the file in Step (8) and copy them back to order place i.e.,

error-926-9.png

  1. Restore the management studio window and go to Object Explorer section. Now right-click on Server Instance and choose Start option

error-926-10.png

  1. Click on Refresh button of the Management studio in order to update the changes

Tip: You can also press F5 to refresh the data of the database file

error-926-11.png

Solution 2: Fix SQL Server Error Code 926 By Executing Commands in SSMS

The procedure includes the execution of some SQL commands in its query window for fixing the problem.

  1. Launch SSMS application on your machine and click on New Query button of it

error-926-12.png

  1. Type and execute the following command where, sp_resetstatus will turn off the suspect flag on database. Moreover, this cmd will allow you to change the current mode and status of database in sys.database.
EXEC sp_resetstatus 'DB_name'

Run the following command on server for setting the database in Emergency mode, which will enable only sysadmin users to operate it

ALTER DATABASE DB_Name SET EMERGENCY
  1. Now it’s time to examine the integrity of all database objects with help of following command:
DBCC checkdb 'DB_Name'
  1. After completely analyzing the database integrity it’s time to set the server mode in single user. Hence, run the below-mentioned command to do so:
    ALTER DATABASE DB_Name SET SINGLE_USER
    WITH ROLLBACK IMMEDIATE
    
  2. Write the following command whose purpose is to repair the error and execute it. However, this command allow the server to remove the data that is required for aiding the file
    DBCC checkdb ('DB_Name' ,REPAIR_ALLOW_DATA_LOSS)
    
  1. Finally the last command of the whole procedure whose aim is to set the database mode back to multiple users so that all other users can again access the database
    ALTER DATABASE DB_Name SET MULTI_USER
    

All the solutions that are discussed above will most probably result in data loss from the server. These may fix SQL Server error 926 but will also result in some loss of essential data. Therefore, the best and appropriate way to resolve this error is third party tool like SysTools SQL Recovery. It guarantees that data will be extracted without any loss or change & thus you can remove SQL server 926 error without any compensation.

Different methods to get parameter list of a stored procedure

There was a question in the forum

I was hoping to find an easy way to get a parameter list of a stored procedures parameters. If the procedure has 3 paramaters, I want a list like this:

param1
param2
param3

We will see how to do this post. Consider the following objects

create table products
(
	product_name varchar(100),
	uom varchar(10),
	exp_date datetime,
	category varchar(100),
	qoh int
);

Create the procedure to add data to that table

create procedure proc_add_products
(
	@product_name varchar(100),
	@uom varchar(10),
	@exp_date datetime,
	@category varchar(100),
	@qoh int
)
as
Begin
insert into product_details 
(product_name,uom,exp_date,category,qoh)
select @product_name,@uom,@exp_date,@category,@qoh
end

Now all the parameter names should be listed out.

There are many methods to do this in sql.

1 Use INFORMATION_SCHEMA.PARAMETERS system catalog view

SELECT 
      PARAMETER_NAME,DATA_TYPE 
FROM 
      INFORMATION_SCHEMA.PARAMETERS
WHERE 
      SPECIFIC_NAME='proc_add_products'

2 Use sys.parameters system object catalog view

select 
      t1.name as PARAMETER_NAME, t2.name as data_type 
from 
      sys.parameters as t1 inner join sys.types as t2
      on t1.system_type_id=t2.system_type_id
where 
      object_id=object_id('proc_add_products') 
order by 
      t1.parameter_id

3 Use sys.parameters system object catalog view

select 
      t1.name as PARAMETER_NAME, t2.name as data_type 
from 
      sys.all_parameters as t1 inner join sys.types as t2
      on t1.system_type_id=t2.system_type_id
where 
      object_id=object_id('proc_add_products') 
order by 
      t1.parameter_id

The first three methods will return the following result

PARAMETER_NAME DATA_TYPE
------------------------ -----------------
@product_name varchar
@uom varchar
@exp_date datetime
@category varchar
@qoh int

4 Use sp_help system Stored Procedure

EXEC sp_help proc_add_products

Result

result1

5 Use sp_procedure_params_rowset system Stored Procedure

EXEC sp_procedure_params_rowset proc_add_products

Result

result2

6 Use sp_sproc_columns system Stored Procedure

EXEC sp_sproc_columns proc_add_products

Result

result3