sql-server-2005

Similarity between strings - SQL Server 2005

Hi, I am looking for a simple way (UDF?) to establish the similarity between strings. The SOUNDEX and DIFFERENCE function do not seem to do the job. Similarity should be based on number of characters in common (order matters). For example: Spiruroidea sp. AM-2008 and Spiruroidea gen. sp. AM-2008 should be recognised as sim...

Catching arithmetic overflow in an SQL 2005 UDF

Hey all, I have defined a scalar UDF in SQL Server 2005, which does some calculations on the input variables and returns a float as a result. This UDF needs to be called from within a select statement to have that calculation result in the query output for each record. Now, due to the nature of the calculation, an arithmetic overflow ...

SQL Server identity issue

I have a query like below declare @str_CustomerID int Insert into IMDECONP38.[Customer].dbo.CustomerMaster ( CustomerName , CustomerAddress , CustomerEmail , CustomerPhone ) values ( ‘werw12e’ , ‘jkj12kj’ , ‘3212423sdf’ , ‘1212121′ ) select @str_CustomerID= scope_identity() After execution it returns null in my parameter. I want to ...

How can I iterate over a recordset within a stored procedure?

I need to iterate over a recordset from a stored procedure and execute another stored procedure using each fields as arguments. I can't complete this iteration in the code. I have found samples on the internets, but they all seem to deal with a counter. I'm not sure if my problem involved a counter. I need the T-SQL equivalent of a forea...

shred xml with variable number of nodes

Hi, I have an xml column which contain data like this: <AuthorList CompleteYN="Y"> <Author ValidYN="Y"> <LastName>Alió</LastName> <ForeName>J L</ForeName> <Initials>JL</Initials> </Author> <Author ValidYN="Y"> <LastName>Ortiz</LastName> <ForeName>D</ForeName> <Initials>D</Initials> </Author> <Author ValidYN="Y"> <LastName>Muftuoglu</L...

Why decimal behave differently?

Hello, I am doing this small exercise. declare @No decimal(38,5); set @No=12345678910111213.14151; select @No*1000/1000,@No/1000*1000,@No; Results are: 12345678910111213.141510 12345678910111213.141000 12345678910111213.14151 Why are the results of first 2 selects different when mathematically it should be same? ...

Detect non-closed connections to SQL

I've inherited a very large project in ASP.net, SQL 2005 and have found where some SQL connections are not closed - which is bad. Without going thru every line of code, is there a way to detect if connections are not being closed? Performance counter? as a follow up - how does SQL reclaim unclosed connections. I'm using non-pooled connec...

UNIQUE Constraints in SQL (SQL Server)

Why are UNIQUE Constraints needed in database ? Can you provide any examples ? Primary Key is UNIQUE by default... Understandable as they are referred in other tables as Foreign keys... relation is needed to connect them for rdbms platform... but why would one refer to other columns as UNIQUE, what is benefit of doing so ?) ...

In TSQL (SQL Server), How do I insert multiple rows WITHOUT repeating the "INSERT INTO dbo.Blah" part of the statement?

I know I've done this before years ago, but I can't remember the syntax, and I can't find it anywhere due to pulling up tons of help docs and articles about "bulk imports". Here's what I want to do, but the syntax is not exactly right... please, someone who has done this before, help me out :) INSERT INTO dbo.MyTable (ID, Name) VALUES ...

SQL Rounding Problems in 2005 and 2000

I have a value in the database which is 2.700000002. When I run a query in Management studio in SQL SERVER 2005 I get 2.7. But when I run in SQL SERVER 2000 query analyzer it comes 2.700000002. 2.70000002 is correct why is SQL SERVER 2005 trying to change the value by rounding it or selecting the floor value? ...

Are stored procedures and triggers the same in Sql Server and MySQL?

Hello, I have two stored procedures and one trigger for a class I am taking that are done in sql server 2005. I want to recreate them in mysql with phpmyadmin. Everytime i put them in I get a 1064 error. Is this becuase phpmyadmin does not handle stored procedures or triggers well, or is it becuase the syntax from sql server 2005 to m...

SQL Server 2005 IsNumeric Not catching '0310D45'

I've got this value '0310D45' I'm using isnumeric to check if values are numeric prior to casting to a bigint. Unfortunately this value is passing the isnumeric check. So my query is failing saying: Msg 8114, Level 16, State 5, Line 3 Error converting data type varchar to bigint. What is the simplest way to handle this. I was thinki...

Adding miliseconds to a datetime in tsql INSERT INTO

I'm doing a INSERT INTO query in order to initialize a new table. The primary key is RFQ_ID and Action_Time How could add 1 milisecond to each Action_Time on new record in order to avoid "Violation of PRIMARY KEY constraint" INSERT INTO QSW_RFQ_Log (RFQ_ID, Action_Time, Quote_ID, Note) SELECT RFQ_ID , GETDATE() AS Action_Time , ...

SQL Server Multiple Joins Are Taxing The CPU

I have a stored procedure on SQL Server 2005. It is pulling from a Table function, and has two joins. When the query is run using a load test it kills the CPU 100% across all 16 cores! I have determined that removing one of the joins makes the query run fine, but both taxes the CPU. Select SKey From dbo.tfnGetLatest(@ID) a le...

Parsing XML in a non-XML column

Hi, i am reasonably proficient with SQLServer, but i'm not a DBA so i'm not sure how to approach this. I have an XML chunk stored in an ntext column. Due to it being a legacy database and the requirements of the project i cannot change the table (yet). This is an example of the data i need to manipulate: <XmlSerializableHashtable xmlns...

Multiple Columns as Primary keys

CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) ) The above example is taken from w3schools. From the above example, my understanding is that both P_Id, LastName together represents a Primary Ke...

How to connect to SQLServer 2k5 using Ruby 1.8.7 over W2k3 with active record 2.3.5

Hi all, sorry for the blast. I'm trying to connect to an SQLServer 2k5 using Ruby 1.8.7 over W2k3 with active record 2.3.5. But, when I ran 'rake migrate' it throws the following: rake migrate --trace Hoe.new {...} deprecated. Switch to Hoe.spec. Invoke migrate (first_time) Invoke environment (first_time) Execute environment Execute mig...

Reporting Services SQL Server 2005 File Share Error

Hi, I have a problem with Reporting Services, on an XP box, trying to save subscription reports onto a Windows 7 box over the network. No matter the permissions or accounts used by reporting services it always comes back with "A logon error occurred when attempting to access the file share. The user account or password is not valid." S...

How to use conditional select query ? in SQL Server

How to use conditional select query in SQL Server ? I have a column in which I store date type variable. Now I should retrieve one value from that column and find the difference with the current_timestamp and if the difference is less than 10 mins I should a return 1 and if more than 10 mins I should return 0. Is this possible ? 05-12...

SQL Agent Logon - What is going on?

I have a DTSX package that is called from a SQL Agent Job. The DTSX package references a file at a fixed location (e.g. e:\mssql\myfile.txt). On most machines, this location exists, but on some I have to manually map this (which is not a problem - I know a better solution would be to use package conifgurations to dynamically pull the ...