T-SQL Debugging – Error

SQL Server Debugging/Microsoft SQL Server Management Studio – Error

Last two month I have spent most of time to Debugging T-SQL code. Some certain situation I have noticed T-SQL Debugging given errors. When I’m working only my own SQL server instance, it’s (T-SQL Debugging) working without any problem. But when I’m connected different server and execute procedures with T-SQL Debugging then it will gives me following error,

Unable to start T-SQL debugging. Could not attach to SQL Server process on ’YOUR SERVER’ .click help for more information.

I have work with T-SQL debugging many times but first time I have notice above error. I try to find some information about this error, but I could not found good relative information. Finally I have decided compare my server configuration with error given server instance configuration. Microsoft categorizes most of above errors under [configuration problem]/ [security problem]. So my security comparison gives me the answer for above error.

The problem is I have working under domain account. I connected in above error given server instance using “SA“ user, the problem is “SA” user cannot execute above server T-SQL debugger. In that case I have created my domain account login to above error given SQL server instance.

CREATE LOGIN [DOMAIN\login name] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

GO

Great now it’s working. If anyone have above T-SQL Debugging error hope this post will help you!!!

ISDATE Function doesn’t validate “date” and “datetime2” data types

Last week I have done some R&D stuff reading SQL Server and as well as Oracle. Recently I have come up with issue how to validate Date column. Answer is very simple; Every SQL Server guy’s Answer is ISDATE function and my answer was same. But after few second, again have a complained we can’t use this function it gives error. I got surprised and ask him to send that error with table format,

• Column data type is – date (SQL Server 2008)
• Error,

Msg 8116, Level 16, State 1, Line 3
Argument data type date is invalid for argument 1 of isdate function.

Then I have created some sample T-SQL scripts and check the issue.

USE tempdb
GO
DECLARE @smalldatetime smalldatetime
SELECT @smalldatetime='01/01/2010'
SELECT ISDATE(@smalldatetime);
GO
DECLARE @date date
SELECT @date='01/01/2010'
SELECT ISDATE(@date); --Error
GO
DECLARE @datetime datetime
SELECT @datetime='01/01/2010'
SELECT ISDATE(@datetime);
GO
DECLARE @datetime2 datetime2
SELECT @datetime2='01/01/2010'
SELECT ISDATE(@datetime2); --Error
GO

Finally I realize the problem ISDATE function, only working datetime, smalldatetime data types. Funny thing is Microsoft did not update the ISDATE () function to support this data types. Even it’s not supported SQL SERVER 2008 R2. Simply it doesn’t validate “date” data type and “datetime2” data type. After checking details for this issue Microsoft promised to consider this in there next release (I didn’t check this issue using SQL Server new CTP1 release (”Denali”))

Some doubt about integer (INT) data type columns

Some doubt about integer (INT) data type columns

Recently I have some doubt about integer (INT) data type column. Let me explain this doubtful problem, if you insert or update blank records to integer column it will update as 0.But if it is decimal data type situation bit different. Check following example,

use tempdb
go
drop table T1
go
create table T1(col1 int primary key
,col2 tinyint
,col3 smallint
,col4 int
,col5 bigint
,col6 decimal(9,1)
,col7 varchar(5));
go
select * from T1
go
begin tran
insert into T1 select 1,11,111,1111,11111,111111,'R1'
insert into T1 select '','','','','',-1,''
commit tran
go
select * from T1
go

If you try following example then you may come up with this error,

begin tran
insert into T1 select 1,11,111,1111,11111,111111,'R1'
insert into T1 select '','','','','','','' --error
insert into T1 select ' ',' ',' ',' ',' ',' ',' ' --error
commit tran

Result,
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Finally I come up with some conclusion but I have some doubt when we dealing with decimal data type. MSSQL consider blank value as 0 if you use only integer data type but if you dealing with string data type(varchar) it will remain as same. I will try to find more information about decimal data type and update it in future post.

How to add WHERE clause after execute procedure returns values?

Let me start like this “Do we have any mechanism to add WHERE clause after execute procedure returns values?” Answer is YES but we need to spend bit time. But recently I found smart Answer for above Question.i will explain here my old concept and new concept,

Old concept,

USE AdventureWorks2008
GO
--create table
CREATE TABLE MyRecords
(col1 int,
col2 varchar(25)
);
GO

--insert Records
INSERT INTO MyRecords
SELECT TOP 15 [object_id],[name]
FROM sys.objects
WHERE (LEN([name]) BETWEEN 0 AND 25)/* cannot add less-then sign because of html validation */
ORDER BY [object_id];
GO

--create procedure
CREATE PROCEDURE proc_get_records
AS
SET NOCOUNT ON
SELECT col1 [object_id], col2 [name] FROM MyRecords;
SET NOCOUNT OFF
GO

--execute sp
EXEC proc_get_records; --this will returns 15 rows
GO

If you need to check your procedure returns values contain some specific value, then you have to do following mechanism.

--if we need to check procedure returns value contain object id 7
CREATE TABLE #tempTable
(col1 int,
col2 varchar(25)
);

INSERT INTO #tempTable
EXEC proc_get_records;

SELECT * FROM #tempTable WHERE (col1=7);
GO

New concept,

Now you can realize above method very effective but time consuming work.Let’s say your procedure contains many columns with different data types and finally it will returns more than thousand rows. Then no doubt this will be, bit time consuming processes. And sometimes your Production Database environment does not allow you to create temp tables or you might be haven’t any permission to create tables. Then this will be painful problem.This is how you can sort-out above Problem,This is very smart concept I found recently when I’m go through OPENDATASOURCE and OPENROWSET functions.And pls Notice,i`m using same table formats I have created earlier to explain this,

USE AdventureWorks2008
GO
EXEC proc_get_records;
GO
SELECT *
FROM OPENROWSET('SQLOLEDB',
'Server=THARINDU\SQLDEV2008;Trusted_Connection=yes;',
'EXEC AdventureWorks2008.dbo.proc_get_records')
WHERE ([object_id] IN(5,3,7))
ORDER BY [object_id];

Still I couldn’t found good mechanism to do same concept using oracle. Any way I’ll try and Update my blog in future post.

Move Data one table to another table.

When we comes to Table designing and the table is very large table, then we can consider Table-Partitioning is most important factor. using above concept we can gain huge Performance and very easy to manage table containing records. In this topic I’m not going to discuss all about Table-Partitioning. But this is more related Table-Partitioning. If we want to move one particular range of data or all records of data one table to another table then Table-Partitioning come and play big role. This is less time consuming process.

/* Formatted on 2009/12/30 20:36 (Formatter Plus v4.8.7) */
CREATE TABLE sourceTable(
column_num NUMBER(18,0),
column_chr VARCHAR2(2)
)
PARTITION BY RANGE (column_num)
(
PARTITION tab_h1 VALUES LESS THAN
(11),
PARTITION tab_h2 VALUES LESS THAN
(21)
);

--Insert test Records
INSERT INTO sourceTable
SELECT ROWNUM, somecode
FROM (SELECT ROWNUM noofrows,'TD' somecode
FROM dba_objects)
WHERE noofrows < 21;

COMMIT ;

--Check no of records in Table
SELECT count(*)
FROM sourceTable;

SELECT *
FROM sourceTable;



--Create test Table 2
CREATE TABLE DestinationTable(
column_num NUMBER(18,0),
column_chr VARCHAR2(2)
);

--check step 1
ALTER TABLE sourceTable
EXCHANGE PARTITION tab_h1
WITH TABLE DestinationTable;

--or check step 2
ALTER TABLE sourceTable
EXCHANGE PARTITION tab_h2
WITH TABLE DestinationTable;


SELECT *
FROM sourcetable;

SELECT *
FROM DestinationTable;



--drop test tables
DROP TABLE sourceTable;
DROP TABLE destinationTable;



Many time we have done “create table as select …“(PL/SQL), “Inseet into select…”(T/SQL) commands to move data one table to new table or given table. But above methods high costly and time consuming operations (EXCHANGE PARTITION command just take 1 ms to move millions of records). Good news is SQL Server also support above PARTITION method but only SQL Server 2005 and 2008 versions only.

Unfair limitations of Oracle Database 10G

Unfair limitations of Oracle Database 10G

Oracle one of great database Product and it takes more than 50% of market share. But I’m honestly saying Oracle has very unfair limitations. Few of them are,

• Database name length (no of Digits) – 8

• Table, Procedure...Etc name length (no of Digits) – 30

But recently I was come across Oracle Installation Problem,

1. “Invalid command line argument. Execute VSJITDebuggar /? for Help”. Javaw.exe has encountered a Problem and need to close.
2. “An unhandled win32 exception occurred in Javaw.exe [5676] “

I was nearly spent more than 1 week but I couldn’t found any solution. Unfortunately there haven’t any blog or article related this. Luckily Buddy (Buddhika Karunatilake) find out the solution. The problem was my Oracle installation contain folder path too long. (“C:\Documents and Settings\Administrator\Desktop\Oracle\ Oracle-10.2.0.1.0-WinNT-Base\database”) I found few oracle articles about oracle limitation,

1. http://www.oracle.com/technology/products/ias/bpel/htdocs/soa_training_10133_installation_instructions.html - (Check 2. Installing the software)
2. http://download.oracle.com/docs/cd/B19188_01/doc/B15917/limitations.htm
3. http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm
4. http://www.gc.maricopa.edu/business/oracle/docs/Oracle8iDocs/server.815/a67790/ch4.htm

SQL Server 2008 Books Online Has Small mistake.

Recently I was start to complete my Understanding the Basic of Triggers Article Part II hope to include all topics related Triggers in that case I want to explain COLUMNS_UPDATED (Transact-SQL) Syntax because everybody know it`s very useful when we working with triggers . Hope everybody familiar with COLUMNS_UPDATED (Transact-SQL) Syntax so I’m not going to explain it this time (after complete my Article then you can get clear idea so pls wait for while). I want to say there is small mistake in SQL Server 2008 Books Online (November 2009) under COLUMNS_UPDATED (Transact-SQL) category, example of “B. Using COLUMNS_UPDATED to test more than eight columns” under this headline. I’m honestly saying this is not big mistake but my view is Microsoft Technical writing and QA team needs to get responsibility of this mistake. So when we comes to this mistake,

USE AdventureWorks;
GO
IF OBJECT_ID (N'uContact2', N'TR') IS NOT NULL
DROP TRIGGER Person.tr1; <---mistake
GO
CREATE TRIGGER uContact2 ON Person.Contact
AFTER UPDATE AS
IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1) & 20 = 20)
AND (SUBSTRING(COLUMNS_UPDATED(),2,1) & 1 = 1) )
PRINT 'Columns 3, 5 and 9 updated';
GO

UPDATE Person.Contact
SET Title=Title, MiddleName=MiddleName,
EmailPromotion=EmailPromotion;
GO

If you run above SQL statement in first time your AdventureWorks database its runs successfully but if you run it again then it will gives you following error.

Msg 2714, Level 16, State 2, Procedure uContact2, Line 5 There is already an object named 'uContact2' in the database.

Hope now everybody will understand the mistake and it’s not big mistake either it’s not good for company like Microsoft.

small bug in SQL server 2005 developer edition with SP 2

Recently SQL server 2008 also release but SQL server 2005 I notice there has small bug but it’s not the big issue, but SQL server QA team need to find out those things. This error I notice SQL server Developer edition when you create a backup database using graphical view (Not the T-SQL) after finish database backup it show Executing (0 %) I see this error twice normally I’m taking backups 5 or 6 time per day. And I was install SQL server 2005 developer edition with SP 2,

Minimum year value in SQL server 2008(date data type)

Minimum year value in SQL server 2008(date data type)

SQL server 2008 comes with lots of new features and they separated SQL server datetime data type as Date and Time (datetime data type also available).SQL server previous versions (SQL 2000, SQL 2005) there is limit for min date (year-1753) and max date (year-9999). But when you notice SQL server 2008 date data type min date is 0001.

/* Date 24/06/2008 SQL SERVER 2000 and 2005 */ DROP TABLE dateCheck; CREATE TABLE dateCheck(dateCol datetime); GO --can INSERT INTO dateCheck(dateCol) VALUES ('12/31/1753') GO --can't INSERT INTO dateCheck(dateCol) VALUES ('01/01/1752') GO SELECT * FROM dateCheck GO /* Date 24/06/2008 SQL SERVER 2008 */ --datetime data type DROP TABLE dateCheckDateTime; CREATE TABLE dateCheck(dateCol datetime); GO --can INSERT INTO dateCheck(dateCol) VALUES ('12/31/1753'); --can't INSERT INTO dateCheck(dateCol) VALUES ('01/01/1752'); GO SELECT * FROM dateCheck; GO --date data type DROP TABLE dateCheckDate; CREATE TABLE dateCheckDate(dateCol date); GO --can INSERT INTO dateCheckDate(dateCol) VALUES ('01/01/1753'); INSERT INTO dateCheckDate(dateCol) VALUES ('01/01/1752'); INSERT INTO dateCheckDate(dateCol) VALUES ('01/01/0001'); GO SELECT * FROM dateCheckDate; GO

SQL SERVER 2005 - MSXML6 SETUP Fail

SQL SERVER 2005 - MSXML6 SETUP Fail

MSXML6 is the latest MSXML product from Microsoft, and is contain with SQL Server 2005, Visual Studio 2005, .NET Framework (3.0,3.5) , Windows Vista and Windows XP Service Pack 3.When you going to install SQL server 2005, some certain situation you can notice MSXML6 setup fail to install and all setup program will be fail. And when you going to run individual setup files, under SQL server 2005 setup folder (“\ Tools\Setup\msxml6.msi”) it will gives you following error “The system administrator has set policies to prevent this installation”

MS says about this http://support.microsoft.com/kb/928469

Solution;

1)Go to add remove programs and remove MSXML 6.0 Parser if available
2)Then removing few registry values you can sort out this problem.
HKEY_CLASSES_ROOT\Installer\Products\
Go to above path and check product name call “MSXML 6.0 Parser” and remove all the related values and reinstall SQL server 2005 setup.
Related links,
http://blogs.msdn.com/quanto/archive/2005/06/29/434136.aspx

Problem to create an index on a BIT (data type) column (SQL server 2000)

When we try to create on an index to Bit data type column using previous versions (SQL Server 2000) of SQL server it doesn’t display the bit data type columns.

And when we type columns name manually it show the message box “Do you want to delete this index?” then how we create index to bit data type column using SQL server 2000?bit when you notice, to create index using manage indexes property (all task > manage indexes) we can create index to bit data type columns with out any problem.



















And also Good new is SQL server 2005 version you can create an index to bit data type column with out any problem.

NULL values and WITH ROLLUP

When you using WITH ROLLUP, you can get group total and full total within the result set but imagine when you allow null columns and writing T-SQL statement with using WITH ROLLUP for groups by your GROUP BY columns? It calculate correct values but we can’t sort it out.

USE mydb GO DROP TABLE ITEM GO CREATE TABLE ITEM ( categoryCode varchar(10) NOT NULL, itemCode varchar(10)NOT NULL, qty int NOT NULL ) GO INSERT INTO ITEM VALUES('category 1','item 1',5) INSERT INTO ITEM VALUES('category 1','item 2',4) INSERT INTO ITEM VALUES('category 1','item 3',2) INSERT INTO ITEM VALUES('category 2','item 1',12) INSERT INTO ITEM VALUES('category 2','item 2',8) INSERT INTO ITEM VALUES('category 3','item 2',1) GO SELECT isnull(categoryCode,' Full') AS categoryCode, isnull(itemCode,' Total is ') AS itemCode, sum(qty ) totQty FROM ITEM GROUP BY categoryCode,itemCode WITH ROLLUP GO --After setting allow nulls ALTER TABLE ITEM ALTER COLUMN categoryCode varchar(10) NULL ALTER TABLE ITEM ALTER COLUMN itemCode varchar(10) NULL GO INSERT INTO ITEM VALUES('category 1',null,5) INSERT INTO ITEM VALUES('category 2',null,4) INSERT INTO ITEM VALUES(null,'item 3',2) GO SELECT isnull(categoryCode,' Full') AS categoryCode, isnull(itemCode,' Total is ') AS itemCode, sum(qty ) totQty FROM ITEM GROUP BY categoryCode,itemCode WITH ROLLUP GO