INSTR Function In MSSQL

Hi everybody,

I was looking for an equivalent ORACLE INSTR Function in MSSQL but I
don´t found it and I don´t know if it exist so I must to write it and
this is the code. Maybe it will be helful to you:

/************************************************** *************************
Description:
Looks for a string inside another string and returns an integer
that correspond to the position of first ocurrence.
Parameters:
Input:
- strSource. Contains the string where the functions look for the
other string
- strToFind. Contains the string to look for inside strSource
Salida:
- Integer value indicating the position of first occurrence of
strToFind in strSource
************************************************** *************************/

CREATE FUNCTION posSubString
(@strSource varchar(400),
@strToFind varchar(200)) RETURNS int
AS

BEGIN

DECLARE
@position int,
@maxPos int,
@longSubStr int,
@res int,
@strSub varchar(200)

SET @position = 0
SET @res = 0
SET @longSubStr = LEN(RTIRM(LTRIM(@strToFind)))
SET @maxPos = LEN(@strSource) - @longSubStr


WHILE (@position <= @strToFind)
BEGIN
SET @strSub = SUBSTRING(@strSource, @position, @longSubStr)

IF (@strToFind = @StrSub)
BEGIN
SET @res = @position - 1
RETURN @res
END
ELSE
SET @position = @position + 1
END


RETURN @res

END



Alonso



ADVERTISEMENT

Instr Function

Hi,In SQL SERVER 2005 Database I have a field called MedNames with values such as "sodium % 34ml" "desx chloride 9 % 76ml"I need to return the words before and including % so "sodium % 34ml"  should return ""sodium % " Is it possible to do it in a select statement? I need to populate a dropdownlist with the shortened names.Thanks

View Replies View Related

Using Instr() Len() And Mid() Function In SQL Statement

I am using the Instr(), Len(), and Mid() function in my SQL query and I keep getting errors stating those are not recognized functions. IS this correct? are there any equivelants?

thanks

View Replies View Related

String Function - InStr

In VB there is InStr, in Excel you have SEARCH. what function can i use to search a string for a specific string. ie: how can i find where a space is in someones name?

our database has a table that is used for security. it contains a user code, user name and passcode. i need to split the username (currently forename and surname in same field) into two, around the space.

in VB i would write something like
strFName = left(<usernamefield>,InStr(1,<usernamefield>," ")-1)
to get the forename and similar to get the surname. how can i do this in SQL so that a view will supply the data apprpriately?

one of the reasons i want to do this is so that i can sort the users by surname! another is so that i can give options on their usercode - a combination of forename initial and 2 characters from surname.

any help welcomed.

View Replies View Related

I Am Looking For The Function That Is The Same As InStr In Access

Hello there...
I am looking for the function that is the same as InStr in Access for SQL server. I have a column that has format like this.. Lastname,Firstname Middlename...
This column doesn't separate each one of them. However I need to separate Lastname and Firstname and Middlename.. I was told that in Access there is function(InStr) that can find a position of comma and separate it as Lastname like that....
I was searching BOL but I couldn't find like this function in SQL Server..
So I need help:-))))
Because everybody has a different length of the lastname, I have a problem.
I can not use SUBSTRING or LEFT or RIGHT because of the varying position of comma ...

Thanks in advance
Jay

View Replies View Related

Instr (vbscript) Function Explanation ???

I went to Microsoft to find some info about the function Instr. I need to
perform a search with a string similar to their example I found below. Can
anyone explain to me Microsoft's example?? I am little confused by the
parameters used and the explanation it gives back to me??

Dim SearchString, SearchChar, MyPos
SearchString ="XXpXXpXXPXXP" ' String to search in.
SearchChar = "P" ' Search for "P".
MyPos = Instr(4, SearchString, SearchChar, 1) ' A textual comparison
starting at position 4. Returns 6.
MyPos = Instr(1, SearchString, SearchChar, 0) ' A binary comparison
starting at position 1. Returns 9.
MyPos = Instr(SearchString, SearchChar) ' Comparison is binary by default
(last argument is omitted). Returns 9.
MyPos = Instr(1, SearchString, "W") ' A binary comparison starting at
position 1. Returns 0 ("W" is not found).

My problem is this:

I need to scan within SearchString for blanks/spaces characters. When I
find one, then place the values to the left and right of it in seperate
columns. For example, I would need to scan 'John Smith A' and then place
'John' in FirstName column, 'Smith' in LastName column, and 'A' in MidName
column.

I think this is how my code would read, but I am confused on how to place
the results into my table to the correct columns?

my search string would be SearchString = 'John Smith A'
my SearchChar would be SearchChar = ' ' (note I am searching for a
space/blank character)

So would then my code be like:

Dim SearchString, SearchChar, MyPos
SearchString = 'John Smith A'
SearchChar = ' '
MyPos = Instr(1, SearchString, SearchChar, 0)

How do I get whatever is returned from the Instr function to a column in a
table??

Any help would be great.

Rey

View Replies View Related

INSTR Function In TransactSQL Of SQLServer 7

Hi
My question is about the INSTR function of MSAccess
I want to know


Goodbye at every
I would you like to know if in TransactSQL of SQLServer7 exist a function that return the position of a string into an other string .
that is a Instr function MSAccess like

INSTR([Start,]expression1,expression2[,Comparison])

I'm sorry for my bad English
Thank you for everything

Emiliano

View Replies View Related

Instr Function For MSQL Query

Hi All

My SQL is extremly rusted so I need some help with a very basic function. I have a character field which is built up using a category code + '-' + number. The problem I have is that the category codes are all different lengths and the items were added using 9 instead of 09. I'm trying to clean up the data so that the same item with e.g. category code DZ20 cannot be added as DZ20-1 and DZ20-01. How do I find the position of the '-' in the Query Analyser for MSSQL 2000?

View Replies View Related

Aggregate Function With MSSQL 7.0

with sybase-sql it is possible to use a SELECT-statment like this:

(select list(string(column_name,
if "order"='A' then ' ASC' else ' DESC' endif))
from SYS.SYSIXCOL join SYS.SYSCOLUMN
where index_id=SYSINDEX.index_id
and SYSIXCOL.table_id=SYSINDEX.table_id)

the result will be like:
field1 asc, field2 asc, field3 desc.....

How can i manage this with MSSQL 70 ?

I havn't found any function working like this.

Any suggestions ?

thankx from Germany

View Replies View Related

Need Help With Raiserror Function In Ado.net Mssql 2000

I have a stored procedure that raises an error just fine, but the problem is when i want to catch that particular error in client code, how do i go able doing so, i want to display a message in a label when that particular error occurs.

right now all i know about the raiserror function is RAISERROR("Message", Severity, Stage) how do i give it number so i can catch it in code?

ALTER Stored Procedure [dbo].[spName]

..............................................
...............................................


IF (@DummySetKey IS NULL)

BEGIN
RAISERROR('Error: This setkey number does not exist as a valid entry.', 11, 1);

SELECT @ErrCode = @@ERROR
END

IF @ErrCode <> 0 GOTO ERROR_HANDLER

ERROR_HANDLER:
ROLLBACK TRANSACTION
RETURN @@ERROR



client code:

Public Function ExecuteNonQuery(byval connstr as string) as boolean

dim conn as new SqlConnection(connstr)
Dim cmd as sqlCommand(spName, conn)
cmd.CommandType = CommandType.StoredProcedure

conn.Open()

try

dim rowsAffected as Integer = cmd.ExecuteNonQuery()

If ex.ErrorCode = 5021 Then
' display in textbox
Else
Throw
End If
Finally
conn.Close()
end try

if rowsAffected > 0 Then
return true
else
return false
end if
End Function

View Replies View Related

MSSQL 2000: How Can We Track Table/sp/function Changes?

My company would like to start keeping track of everytime a table, stored procedure, or function is changed.

What is the best way to do this?

View Replies View Related

USE InStr() For ASP

val = "the snow (hh/ffff[dg]fd) "

 

v1 = inStr(1,val,"/") or v1 = inStr(1,val,"[") or  v1 = inStr(1,val,"(")

 

it have error :

 Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'inSrt'

 

v1 = inStr(1,val," ") is OK

 

How do it correct

 

Thanks

View Replies View Related

Instr() Equivalent In SQL Server

I am trying to convert a complex function from Oracle to SQL Serverand have come across Oracle's Instr() function. I see SQL Server hasCHARINDEX() which is similar, however it does not provide some keyfunctionality I need. Here is an example of the Oracle code:if Instr( sTg , cDelim, 1, 3 ) > 0 thensd := SubStr( sTg, Instr( sTg , cDelim, 1, 1 ) + 1, Instr( sTg,cDelim, 1, 2 ) - Instr( sTg , cDelim, 1, 1 ) - 1)end if;Has anybody converted anything similar to this within SQL Server? Anyhelp is GREATLY appreciated!Thanks.

View Replies View Related

Find? Instr? Indexof?

is there a sql keyword for find or instr?

i have a field i wish to make into two and i need the position of a string "-" in the field so i can do a select right and copy that data to a new colm

View Replies View Related

Using CURSOR For INSTR Purposes

TASK: At my work we want to categorize and summarize all our IIS web logs and make statistics from it and such. What I need to do is take the browser type from a certain column in the table. All the information is stored in 1 column, and I figure an instr function would be best to do this. I am new to SQL, so I was told to look up the cursor function. In summary, I want to take all the IIS data and match it up against a defined table and then have a sum function for each browser.

Here are some examples of what the column data looks like: (found in the [csMethod] column

Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1)
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+2.0.50727)
Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.04506.30;+InfoPath.2;+.NET+CLR+1.1.4322)
Mozilla/5.0+(Windows;+U;+Windows+NT+5.1;+en-US;+rv:1.8.0.8)+Gecko/20061025+Firefox/1.5.0.8


I made a define table which lists an ID (primary key) and instr to search for as well as the full browser name. (define.browser)

DEFINE.BROWSER

ID# INSTR# BROWSER NAME
###############################
1___Opera+7_______Opera 7
2___Opera/9_______Opera 9
3___Safari/_______Safari
4___Firefox/1.0___Mozilla Firefox 1.0
5___Firefox/1.5___Mozilla Firefox 1.5
6___Firefox/2.0___Mozilla Firefox 2.0
7___MSIE+5.5______Microsoft Internet Explorer 5.5
8___MSIE+5________Microsoft Internet Explorer 5
9___MSIE+6________Microsoft Internet Explorer 6
10___MSIE+7________Microsoft Internet Explorer 7
11_________________OTHER BROWSER

I am having problems getting a cursor to work. Are there any good tutorials out there, or can anyone be of assistance. Thank you in advance.

View Replies View Related

InStr To Remove : And , And Sum Data

 

Greetings All

I have some data-- specifically times for cell phone usage in the format of (7:00, 15:51, 1,200:45, etc).  I need to find a way to remove the ":" and the ","-- sum the data and then return it to its previous format of (7:00, 15:51, 1,200:45, etc).  Does anyone have some code they could post??

thanks as always

km

View Replies View Related

Equivalent Of Oracle's INSTR( With 4 Parameters) In SQL Server

The syntax for Oracle's INSTR function is

instr (string1, string2, [start_position], [nth_appearance])

string1 is the string to search.

string2 is the substring to search for in string1.

start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.

nth_appearance is the nth appearance of string2. This is optional. If omiited, it defaults to 1.

In SQL Server, we are having CHARINDEX and PATINDEX functions. But they will not accept the fourth paremeter (nth_appearance)

Do anybody know the solution for this ????

View Replies View Related

How Many Result-rows Does Mssql Return Should Be Used Asynchronous Method To Use Mssql Cursor?

How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?

i want to make the cursor fast in any time whatever how many results returned

View Replies View Related

Database Migration Plan - (mssql/msde To -&> Pgsql/mssql)

Hi,

i was planning to create a database migration tool ..
its a certain database of a DMS (document management system) to
another DMS (two different DMS)... from DMS using msde 2000 server .. and tranfer to a DMS using a postgre sql or mssql .. depends ..

they have different table structures and names . . :D

i was thing of what language shall i use.. or what language is the best to work on this kind of project :)

hoping for your kind help guys. thanks :)



br
Frozenice

View Replies View Related

Server Configuration For MSSQL 2000 And MSSQL 2005

Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache

View Replies View Related

Differenece Between Mssql Sp3 And Mssql Sp4 - Urgent Reply Is Required

is there any differernce between mssql sp3 connection establishment and mssql sp4 connection eslablishment while using java jdk1.4 to connect to...

i want to use com.microsoft.sqlserver.SqlServerDriver driver for connection instead of sun.jdbc.odbc.JdbcOdbcDriver and DNS

View Replies View Related

Creating Index In MSSQL 2000 From MSSQL 2005

Hi,
 
I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.
 
Ex:- 

CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY

(ACTIVITY_NAME ASC, PROCESS_NAME ASC, START_TIME ASC, ISMONITORED ASC)

INCLUDE

(INSTANCE_ID, ACTIVITY_TYPE, STATUS, END_TIME, ORGANIZATION);
 

Any help in creating such indexes in 2000 version is welcome.
 
Thanks,
Suresh.

View Replies View Related

Generating Script For MSSQL 2000 From MSSQl 2005?

Hello
We are using SQL 2005 and now we are planning to use SQL 2000. what are the ways to do the process.
 
We taken the script spcificall for 2000 and run it in SQL 200. But we are getting the error in SCRIPT?
 
Could you please give me the step to do?
 
Thanks,
Sankar R
 

View Replies View Related

Run Mssql 6.5 Client And Mssql 7.0 Server On Same Nt Workstaton?

Hello:

I am currently work on mssql 6.5. On my workstation, I have mssql 6.5
cient software.

However, I would like to install mssql 7.0 server on my nt workstation
and work with it to become familiar with 7.0. Can I install mssql 7.0
server on my nt workstation? Can mssql 6.5 client coexist with mssql 7.0
on the same machine if they are in different directories?

Thanks.

David Spaisman

View Replies View Related

MSSQL Express 2005 Vs. MSSQL 2000

Ben writes "I have a sql script that doesn't function very well when it's executed on a SQL 2000 server.

The scrpt looks like this:


---------------------------------------------------------------------------------------------------
USE [master]
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
EXEC sp_addlogin N'SSDBUSERNAME', N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
ALTER LOGIN [SSDBUSERNAME] WITH PASSWORD=N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ')
DROP DATABASE [ISIZ]
GO
USE [SurveyData]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
GO
USE [SurveyManagement]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
---------------------------------------------------------------


I need to be converted to a script that can be executed on both MSSQL 2000 and MSSQL 2005.

I was wondering if somebody there could help me with this problem?!

Thanks,
Ben"

View Replies View Related

Migration MSSQL 2000 32-bit To MSSQL 2005 64-bit

I've been tasked to move our production databases on MSSQL 2000 to 2005. I've supported MSSQL since version 6.5 and performed migrations to successor versions.

Current Environment is MSSQL 2000 32-bit with current Service Packs.

I've performed mock migrations on Test servers upgrading all Production instances simultaneously from MSSQL 2000 to 2005 32-bit. The Test environment is identical to Production minus server name, IP etc. Also I have a separate server with MSSQL 2005 installed where I use the DETACH / ATTACH and BACKUP / RESTORE method for migration / acceptance testing. There are approximately 30 databases totaling 70 GB. This has gone as expected and fairly successful. Vendors have been coordinated with to update code and staff for acceptance testing.

I'd prefer going directly to MSSQL 2005 64-bit instead if possible due to memory benefits etc. This is where I'd like some feedback prior to borrowing a 64-bit server for testing.

Upgrade options:

1. Is it better to migrate from MSSQL 2000 32-bit to 2005 64-bit via:
a. DETACH / ATTACH
b. BACKUP / RESTORE
c. Is one method more advantageous relating to the end result?
2. Regarding XP clients, have issues been experienced with the default SQL Server driver or is an alternate recommended for XP clients to connect to a MSSQL 64-bit server databases?
3. If you have performed this migration and have relevant experience please pass them along.

View Replies View Related

Deploying MSSQL 2005 Express DB To MSSQL 2005 WKGP Errors

DB is developed on local computer with MSSQL 2005 Express. My host is on MSSQL 2005 workgroup. Are they compatible, because I am getting errors? Is my approach wrong?

I have tried several approaches.

A) I created a backup of database on my local, then placed a copy on the server. Then I tried to restore through Server Management Studio. I get this error. 

TITLE: Microsoft SQL Server Management Studio

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

ADDITIONAL INFORMATION:

The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.

RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3169)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=3169&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

 

B: I also have tried copying the database. I put it in the same path as the other databases that can be read with server management studio on the server. Then, tried to get to it through server managements studio and it did not appear. So I tried to attach it. Then I received this error:

TITLE: Microsoft SQL Server Management Studio

------------------------------

Attach database failed for Server 'MROACH1'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

Could not open new database 'LodgingDB'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

 C: I have also tried opening the Database, and back up file through Server Management Studio. without success. 

 D: I also tried Windows and Software update at microsoft update, but no updates were recommended for Version on Server.

I'm surprised this is so hard. My original data base was created in same family of software. 2005 MS SQL Express. I could use some direct help from someone experienced with this. Am I doing it wrong or are the DB versions incompatible.

Mark Roach

 

 

 

View Replies View Related

Conversion Of MSSql 2000 Database To MSSQL 2005 Database

How to convert a database in MSSQL 2000 to MSSQL 2005 database.Is there any tool or documentation available for this?

View Replies View Related

Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function

Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark

View Replies View Related

Help Convert MS Access Function To MS SQL User Defined Function

I have this function in access I need to be able to use in ms sql.  Having problems trying to get it to work.  The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String   Dim strReturn As String   If IsNull(strField) = True Then      strReturn = ""   Else      strReturn = strField      Do While Left(strReturn, 1) = "0"         strReturn = Mid(strReturn, 2)      Loop   End If  TrimZero = strReturnEnd Function

View Replies View Related

In-Line Table-Valued Function: How To Get The Result Out From The Function?

Hi all,
 
I executed the following sql script successfuuly:
 
shcInLineTableFN.sql:

USE pubs

GO

CREATE FUNCTION dbo.AuthorsForState(@cState char(2))

RETURNS TABLE

AS

RETURN (SELECT * FROM Authors WHERE state = @cState)

GO
 
And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.
 
I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:
 
shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO

 
I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.

 
Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.
 
Thanks in advance,
Scott Chang

View Replies View Related

With Recompile: Mssql 6.5 Vs Mssql 7.0

Hello:

We are now on mssql version 7.0, sp2. The application was upgraded from
mssql 6.5 to mssql 7.0 several weeks ago


Although we are not suppose to do so, two stored procedures were put into
production with the "WITH RECOMPILE' option under mssql 7.0, sp2.

During a nightly run yesterday, these 2 procedures ran extremely slow and
blocked others trying to run other stored procedures. We know why becuase
of the behaviour of the 'with recompile' option.

In researching this, we noticed that Microsoft speaks in mssql 7.0about
the possible slowness if there are 'temp' tables involved here but these 2
procedures do not create or use 'temp' tables.

We removed the 'with recompile' option statements from the two stored
procedures and they ran fine and quickly.

The with recompile was put there because of arithmatic overflows with some
data in . Since we took them out, we will need to get a workaround.
My concerns is:

1) The stored procedures 'with recompile' under mssql 6.5 ran fine but
under mssql 7.0, the stored procedures ran extremely slow. Does any one
have any information on why stored procs with 'with recompile' would run
differently under mssql 6.5 and mssql 7.0?

Any information would be greatly appreciated. Thanks.

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved