Uncommitted transactions causing timeout error

The timeout period elapsed prior to completion of the operation or server is not responding

@@TRANCOUNT helped identify uncommitted transactions and the issue was resolved.

WHILE @@TRANCOUNT > 0
BEGIN
  COMMIT;
END;

Return code warning and truncation error in SQL Server

Msg 8152, Level 16, State 14, Procedure my_procedure, Line 56
String or binary data would be truncated.
The statement has been terminated.
The 'my_procedure' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

Trying to figure out the reason for this error, i am able to modify the data and it procedure executes successfully. The issue is the column the procedure is updating is of length 30 characters and the data being updated is 17 characters (including a white space 'errorhere string'). As soon as i remove the white space and the 'string' it works like a charm.

Tried removing the white space but did not help either, still get the same error.

To resolve the NULL value being returned, just added return (0) and it was gone. I was under the impression that by default if no return is specified a 0 (numeral) is returned by the procedure.

Google is my friend....

Quick Tip #1

Converting a INT to VARCHAR trims all leading zeros

SELECT CONVERT(VARCHAR, 00012345)

Generating view definition in SQL Server

Make sure you have the privileges/access to the these commands.

1. sp_helptext [my_view_name]

2. Right click on the view in object explorer and generate a script

3. Run the query on information_schema.views table to see view definition

Hope this helps, i am also learning and Google is my friend :)

SYSOBJECTS.TYPE Distinct Values

use my_db
go

select object_id, type from susobjects;
go


Most Frequently Used      
P stands for Stored procedure
U stands for User table
F stands for Foreign Key constraint
PK stands for Primary Key constraint
V stands for View

Less Frequestly Used
C stands for Check constraint
UQ stands for Unique constraint
RF stands for Replication Filter stored procedure
TR stands for Trigger                       

Never Used
X stands for Extended stored procedure
S stands for System table
L stands for Log
D stands for Default constraint

(NOLOCK) vs. SET TRANSACTION ISOLATION LEVEL

As the title says, is there any difference between these 2 when it comes to procedures and/or SQL statements?

From what i have read and experienced, at a procedural level both of these are the same. But i would always prefer NOLOCK hint as i have gotten used to it so much that i always think something is not right with the code.

In SQL statements it has to do with caching (no personal experience here), read it from an online forum. Its better to use NOLOCK when running SQL statements.

Error when using a temp table and join with a physical table

Well i have never seen this exception before,  a good day to learn something new:



select * from dbo.[a]

create table tempdb.#b (temp_number varchar(11))

insert into tempdb.#b (temp_number)
select dnb_number from openquery ([XYZ], 'select dnb_duns_number from dnb_store_data')

create nonclustered index temp_number_idx on tempdb.[#b] ([temp_number])

select * from tempdb.[#b] a (nolock)
where not exists (
select [matched number] from dbo.[a] b (nolock)
where a.[temp_number] = b.[matched number])


(381618 row(s) affected)
Exception has been thrown by the target of an invocation.

(1 row(s) affected)

On searching found out it might be connection or network issue. Is that linked server [XYZ] causing this? Not sure, i get it when i try to execute the last select statement that uses NOT EXISTS

Error occurred during the pre-login handshake

A connection was successfully established with the server, but then an error occurred during the pre-login handshake.

Update: Not getting this error anymore, might be a temporary glitch on the server or the network side.