Yet another Java Developer.

JSF Developer living in Oklahoma City

Gallery

imgp0255-large.jpg IMGP0165.JPG IMGP0016.JPG IMGP0055.JPG

Currently browsing sql

Show last executed query SQL Server - Disected

April 22nd, 2010 in Uncategorized | No Comments »

I found this online, without explanation so I will dissect the query and explain what is happening.

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

This beautifully crafter query will show us last few queries executed on they db, very usefull for seeing what is Hibernate generating for us.

Why do I get a java.sql.SQLException: “Unable to get information from SQL Server” when trying to connect to an SQL Server instance?

March 26th, 2010 in Uncategorized | No Comments »

Possible solution 1

I am using jtds driver so I would suggest checkingout their proposed solution first here http://jtds.sourceforge.net/faq.html#instanceGetInfo

Possible solution 2

Their solution did not for me so here is what I did :
From cmd prompt run
sqlcmd -L and make sure that the server you are connecting is listed in the returned list, if its not then there is your problem.
Simply restarting ‘SQL Browser’ and ‘SQL Server’ should work, run you sqlcmd -L command and make sure that your server is visible in the list.

Remove/Change default constraints sql 2000/2005

June 9th, 2009 in Uncategorized | No Comments »

Problem

When we add default constraint on a field it is automatically assigned a name in following format

Format:
DF__tablename__PARTOFFIELDNAME__HASHCODE
Example:
DF__scheduled__CREAT__00DF2177
DF__scheduled__MODIF__01D345B0

HashCode part of the format is different on each database so we can’t just find the name of constraint and use that in our alter script. That’s when sysobjects table comes to the rescue.

This will list all the default field values for all tables

SELECT OBJECT_NAME(ID) AS NameofConstraint,
OBJECT_NAME(parent_obj) AS TableName
FROM sysobjects
 WHERE xtype = 'D'

from here we can construct our TSQL script to suit our needs.

Solution

  1. Get default fields of interes t(ConstraintName, TableName)
  2. Drop each constraint
  3. Add new default constraint with a NAME
/**
@Desc: Remove default constraints from a given table, then add new default constraint
2000/2005 compatible
@Author Greg B.
**/
USE [mydb]
GO
 
BEGIN TRANSACTION
GO
 
Declare MyCursor Cursor FOR
 
SELECT OBJECT_NAME(ID) AS NameofConstraint,
OBJECT_NAME(parent_obj) AS TableName
FROM sysobjects
 WHERE xtype = 'D' 
AND (OBJECT_NAME(parent_obj) = 'procedures' AND  OBJECT_NAME(ID) LIKE '%DF__procedure__INHER%')
 
OR 
(
 OBJECT_NAME(parent_obj) = 'subarea'
AND   (OBJECT_NAME(ID) LIKE '%DF__subarea__ABS_INH__%') 
 OR (OBJECT_NAME(ID) LIKE '%DF__subarea__ASSIGNE__%') 
) 
 
 
DECLARE @SQLScript NVARCHAR(300)
 
Declare @NameofConstraint VARCHAR(255) 
Declare @SchemaName VARCHAR(255) 
Declare @TableName VARCHAR(255) 
Declare @ConstraintType VARCHAR(255) 
 
Open MyCursor
Declare @Count int
SELECT @Count = 0 
FETCH NEXT FROM MyCursor INTO @NameofConstraint, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
	--PINT 'RECORD='+@NameofConstraint +' :: '+@TableName
	SELECT @SQLScript= 'ALTER TABLE '+ @TableName +' DROP CONSTRAINT '+@NameofConstraint
	EXEC sp_executesql @SQLScript
	SELECT @Count=@Count+1
	--Advance to next record
	FETCH NEXT FROM MyCursor INTO @NameofConstraint,@TableName
END
 
Close MyCursor
DEALLOCATE MyCursor
 
IF @Count != 0
BEGIN
print 'Adding alter'
-- Now we add the constrains back again to the tables with standarized names
  ALTER TABLE [dbo].[procedures] ADD CONSTRAINT DF_PROCEDURE_INHERENT_RISK DEFAULT ((1)) FOR [INHERENT_RISK]
  ALTER TABLE [dbo].[subarea] ADD CONSTRAINT DF_SUBAREA_ABS_INHERENT_RISK DEFAULT ((1)) FOR [ABS_INHERENT_RISK]
  ALTER TABLE [dbo].[subarea] ADD CONSTRAINT DF_SUBAREA_ASSIGNED_INHERENT_RISK DEFAULT ((1)) FOR [ASSIGNED_INHERENT_RISK]
END
 
COMMIT TRANSACTION 
 
IF @@TRANCOUNT > 0
BEGIN
    ROLLBACK TRAN
END 
GO

Alter column size mysql - ms sql

April 27th, 2009 in Uncategorized | No Comments »

Changing column size in both MySQL and MS SQL Server. When changing from wider to narrower size data might be truncated so be aware of that.

MySQL

 ALTER TABLE workbook MODIFY  NAME varchar(255)

MS SQL

ALTER TABLE workbook
ALTER COLUMN [NAME] VARCHAR(255) NULL
GO

Columns that part of primary key can’t be altered.

Detect SQL server version

April 13th, 2009 in Uncategorized | No Comments »

Sometime we need to provide one script that works on multiple server, here is how to do in for SQL Servers 2000 and 2005.

We will use simple technique to detect the server type using the serverproperty('ProductVersion') for a list of options visit

SERVERPROPERTY (Transact-SQL). So lets see what are the pros and cons

of this technique.

Pros

This technique allows us to use either SQL 2000 or 2005 servers.

Cons

Cant’ just use new sql 2005 syntax since 2000 does not recognize it, so we are executing it with exec after the new code string have bean concatenated.

This could easily leave us open to sql injection if we are not sanitizing the input correctly in web environment.

Another approach would be to execute a script file with the osql or sqlcmd

 
              EXEC master..xp_cmdshell 'OSQL -S devserver -U sa -P pass -ic:\script2000.sql -n'
 
              EXEC master..xp_cmdshell 'SQLCMD -S devserver -U sa -P pass -ic:\script2005.sql'

Complete Example

 
    DECLARE @sql2005Code varchar(255)             
 
    SET @sql2005Code = 'print ''SQL 2005 Code'' '
 
    DECLARE @ver nvarchar(128)
 
    SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)
 
    SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)
 
            -- SQL 2000
 
    IF ( @ver = '8' ) BEGIN
 
            print 'SQL 2000'
 
    END
 
            -- SQL 2005
 
    ELSE IF ( @ver = '9' )BEGIN
 
      exec (@sql2005Code)
 
    END

Sidebar3 : Please add some widgets here.