Archive

Archive for the ‘MSSQL’ Category

SQL makes decision for you – strange

June 11, 2010 Leave a comment

I ran into a strange MSSQL behaviour.

I wanted to do some calculation and output it in my application like this:

Declare @aFlaot float

set @aFlaot = 3*1/2

select @aFlaot aFlaot

From above I was expecting a float value as I was storing it in a float type variable but it was returning 1 ( as an integer)

And when I tried following I got back right value with correct decimals

Ex 1)

set @aFlaot = 3*1.0/2

select @aFlaot aFlaot

Returned 1.5 (as expected)

Ex 2)

declare @aVal float
set @aVal = 1

set @aFlaot = 3*@aVal/2

select @aFlaot aFlaot

Returned 1.5 (as expected)

What I understand is SQL decides on the basis of values you are using in your calculation not the container type you are using.

Advertisements
Categories: MSSQL, SQL

Finding file extension in SQL

June 1, 2010 Leave a comment

Here is the SQL code to find the extension of a file:

declare @appurl varchar(50)
set @appurl= reverse(‘philip.singh.bedi.doc’)

select reverse(LEFT(@appurl, CHARINDEX(‘.’, @appurl) – 1)) ext,
reverse(RIGHT(@appurl, LEN(@appurl) – CHARINDEX(‘.’, @appurl))) fname

Categories: MSSQL, PLSQL

Coldfusion 8 Query feature

May 29, 2010 Leave a comment

This post is the extension of my earlier post – 10 March 2009

Actually I wrote about getting Identity column value, when inserting a new record in MSSQL data base. but few days ago I found out from Ben Forta’s blog, here is the link to that blog and content:

If you are using a database table with an identity (auto-increment) field, ColdFusion 8 can automatically return the newly created field value for you, without needing a trigger or a subsequent . All you need to do is look in the optional RESULT structure, and if available the value will just be there. This feature is very DBMS and driver specific (and unfortunately does not seem to be supported by Apache Derby), and the name of the structure member containing the generated value is different based on the DBMS being used. Here are the ones you should be aware of:

* SQL Server: result.IDENTITYCOL
* Oracle: result.ROWID
* Sybase: result.SYB_IDENTITY
* Informix: result.SERIAL_COL
* DB2: result.KEY_VALUE
* MySQL: result.GENERATED_KEY (MySQL 4 and 5 only, MySQL 3 does not support this feature)

Ben Forta’s Article link

Categories: ColdFusion, MSSQL, MySQL

How to find Function, Stored Proc, Table, View Details

May 27, 2010 Leave a comment

In Microsoft SQL Enterprise Manager

Open The query Tab for the DB

Write the object name (just name without DBO etc)

Select the object name and press Alt + F1

Will list all information related to that object
i.e. For function

Will list
1) Creation /owner etc details
2) Output details with type etc
3) Input needed by that function

Categories: Database, MSSQL

Converting a List to table

October 13, 2009 Leave a comment

This function accepts a String which holds list of id’s and returns a table and then you can use this table to join and filter main table data. You can pass the list with your choice of separator.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create function [dbo].[ListToTable]
( @list varchar(4000), @separator varchar(10) )

/* this function receives a @separator delimited list and outputs a table with
each item in the list as a row in a table so the following can be used
SELECT * from dbo.Customers
inner join listToTable( ‘123,456,789’, ‘,’ )
on CustomerID = listValue
*/

returns @listTable table( listValue Int null )
as
begin

if @list is null
insert into @listTable values( null )

— @list is the list we wish to parse
— @Separator is the separator charactor such as a comma
declare @separator_position int — This is used to locate each separator character
declare @list_value varchar(1000) — this holds each list value as it is returned

— For my loop to work I need an extra separator at the end. I always look to the
— left of the separator character for each list value
set @list = @list + @separator

— Loop through the string searching for separator characters
while patindex(‘%’ + @separator + ‘%’ , @list) 0
begin

— patindex matches the a pattern against a string
select @separator_position = patindex(‘%’ + @separator + ‘%’ , @list)
select @list_value = left(@list, @separator_position – 1)

insert into @listTable values( @list_value )

— This replaces what we just processed with and empty string
select @list = stuff(@list, 1, @separator_position, ”)
end

return

end

Categories: MSSQL, SQL

Finding file extension in SQL

October 13, 2009 Leave a comment

Here is the SQL code to find the extension of a file:

declare @appurl varchar(50)
set @appurl= reverse(‘philip.singh.bedi.doc’)

select reverse(LEFT(@appurl, CHARINDEX(‘.’, @appurl) – 1)) ext,
reverse(RIGHT(@appurl, LEN(@appurl) – CHARINDEX(‘.’, @appurl))) fname

Categories: MSSQL, SQL

SQL Express DSN creation problem

September 19, 2009 Leave a comment

I had problem creating DSN through ColdFusion administrator for a database in MSSQL 2005 express addition and problem was my tcp port was not enabled for this service and the post/ blog which helped me was this one:

http://www.fusioncube.net/index.php/coldfusion-sql-server-express

Thanks to Steve Brownlee who posted this blog in 2006

Categories: ColdFusion, DSN, MSSQL