Working With Patindex
Nov 7, 2007
hi!
i have one datatable with some file's details like id, name, createdate. i wrote one storedprocedure to search in that table by filename using wildcard character. its also working perfectly. But the problem is i want to arrange the results in ascending order depended by matching level. If I search for 'file123' it should give results in one order by matching level, means - lets think the results are 'fil23', file123', 'file', 'file1'. the results should be in order like 'file123', 'fil23', 'file1', 'file'. the results of the sp which i used, are order by datecreated. Please help me to do this.
the storeprocedure i used -
"CREATE PROCEDURE [dbo].[SP_DYN_SearchinFiles_DynByWildcardsADMIN] @searchparam nvarchar(50)
AS
BEGIN
select
Id,
OriginalName,
ContentType,
FullName,
Department,
DateCreated,
Client,
username
from Files_Dyn where PATINDEX('%' + @SearchParam + '%', OriginalName) > 0
END"
View 5 Replies
ADVERTISEMENT
Jul 19, 2000
Good morning.
I was wondering if someone would point out my flaw. I am attempting to get information separated by spaces, placed into separate columns. I tried without success several times yesterday to get what I desired and was not able to get it done. I am fairly new at developing SQL code and am low on the SQL coding totem pole of experience but would like your help.
Here is what I have so far.
SELECT
CONVERT(char(16), SUBSTRING(c1,
1,
PATINDEX('% %', c1))) SSI_House_Number,
CONVERT(char(15), SUBSTRING(c1,
PATINDEX('% %', c1)+1,
LEN(c1)-PATINDEX('% %', c1)+1)) SSI_Directional
FROM
t1
WHERE
c2 = 4254
My problem arises when I, in the second column, attempt to get only the information BETWEEN the first space: PATINDEX('% %', c1)+1 and the second space. I am coming up short and would like someone to point out my error or assist me in getting the query pointed in the right direction.
Thanks,
Daimon
View 1 Replies
View Related
Feb 13, 2007
Hello everyone:
how do i find a single quote ['] with the patindex function.
SELECT 'a company's name'
thx,
F.
View 5 Replies
View Related
Jul 23, 2005
Hello,I am trying to update a column in a table with an 8 digit string.The 8 digit number is obtained from another column which is a commentsfield.e.g.Comments Field :1) RD-V0216970 RQST NON SMKING KING Unknown # : 223389212) Received verbal authorization Authorization # 0403453) international plaza, singapore # 96722540The code that I am using isUPDATE U SET U.NUM =CASEWHEN U.BOOKED_COMMENTS_TXT LIKE('%[0123456789][0123456789][0123456789][0123456789][0123456789][0123456789]%')THENSUBSTRING(U.BOOKED_COMMENTS_TXT,PATINDEX('%[0123456789][0123456789][0123456789][0123456789][0123456789][0123456789]%',U.BOOKED_COMMENTS_TXT), 8)ELSE NULLENDFROM UNKNOWN1_RESERVATIONS UHere's what my result set looks like1)V02169702)0403453)96722540But this is how I want my result set to look like1)223389212)null3)96722540What I need is a way to restrict the search criteria to exactly 8numeric digits. Any suggestions will be helpful.Thanks in advance
View 11 Replies
View Related
Jul 20, 2005
Hi allUsing SQL Server 2000 SP3 on WIn2K Pro with SP2.When I do thisSELECT PATINDEX('%[%', 'ABC[DEF')GOI get a result of 0, when I would expect a result of 4.This works as I expect:SELECT PATINDEX('%]%', 'ABC]DEF')GOI must be missing something really obvious here Any help would beappreciated.
View 4 Replies
View Related
Aug 21, 2007
Hi all,
My database is not case-sensitive, but I want output like...
SELECT patindex('%[A-Z]%','gaurang Ahmedabad')
The output should be first occurrence of uppercase A to Z, so output should be 9 it should not be 1.
Above query is giving output as 1 bcoz the 1st character in the expression is 'g' and it is in A to Z, but this is not capital 'G'. The 1st capital letter in the expression is 'A' (9th character in the expression).
Is there anyway to achieve this using PATINDEX? or Is there any other way to achieve this?
Thanks,
Gaurang Majithiya
View 3 Replies
View Related
Feb 11, 2007
Hi,
How to search the last occurance of one String in to another string..Using function PATINDEX() in a column of data type Text..
plz Give some solution i really need it ...
Thanks Yogesh
View 7 Replies
View Related
May 1, 2007
Alright, I am trying to write a query that will select a column.. then search through the column and return me the fields with no underscores in them.
i.e we have fields that are like ABCD and ABCD_1. I want to basically return all of them up to the underscore.
so i am trying to use a substring combined with patindex. Here is my statement for patindex. patindex('%_%', short_name)
however, i cannot get patindex to ignore the fact that _ is a wildcard and treat it as character. Ive tried ('%[_]%', short_name) and ive tried ('%!_%', short_name) escape '!' and i just keep getting errors... If someone could help me out, that would be most appreciated. thank you.
View 1 Replies
View Related
Sep 6, 2013
I have a number of columns I'm selecting from a table, and I'd like to use PATINDEX to search for a string within a field. Upon finding this string it would return another string...:
SELECT
CASE PARTNERID
WHEN PATINDEX('%INX%',PARTNERID)>0 THEN 'AXXENT'
ELSE PARTNERID
END AS PARTNERID
However, I'm getting this error:Incorrect syntax near '>'.
View 3 Replies
View Related
May 8, 2008
Hi
I have a table which stores a big chunk of html text, which I have to search for and replace some strings.
How can I write a query that makes use of regular expressions to do the following:
Below is a block of example text
======================================================================
" src="http://aaaa/bbbb/cccc.nsf/blind%2010x10.gif?OpenImageResource" width=10> <img height=10 alt="" src="http://aaaa/bbbb/cccc.nsf/blind%2010x10.gif?OpenImageResource" width=10><img height=11 alt=""
http://aaaa/bbbb/cccc.nsf/blablablabla?OpenDocument src="http://aaaa/bbbb/cccc.ns http://aaaa/bbbb/cccc.nsf/d8d4f0dfa09ba71142256cc50040ee51/ee9b4493dac727be422571c30027df61? OpenDocument" target=_self>
======================================================================
Now I want to search and replace the string that is highlighted in red with something like "FOUND"
The below string is what I want to search for - I will used PATINDEX on the string below to find the start index of the string in the body, and then I will add on 114 to the when doing a replace:
'%http://aaaa/bbbb/cccc.nsf/%some guid%/%some guid%?OpenDocument%'
For example
REPLACE
(
TheField,
SUBSTRING
(
TheField,
PATINDEX(''%http://aaaa/bbbb/cccc.nsf/%some guid%/%some guid%?OpenDocument%', TheField),
,114
)
,
'FOUND'
)
QUESTION:
what would the regular expression be and how does one type it in the query?
I tried something like the below but it does not work, please advise .....
[a-z][0-9]{32} %/% [a-z][0-9]{32}
View 1 Replies
View Related
Dec 18, 2013
I have a value in a column '01-08-087-0101W5'. I would like to break this value down into 5 different columns. The column will be broken down at the dashes , so the 5 columns will have values like
01
08
087
101
5
I would also like to trim any leading 0's in the above values. i have been trying the following
SUBSTRING(column1,patindex('%[^0]%',column1) ,2) AS FROM_TWP
View 9 Replies
View Related
Sep 30, 2014
I want to search a table and display only results that contain particular Hotmail and yahoo email addresses. The code I am using currently is:
Select Name, Email, PATINDEX('%Hotmail%, Email) From tblPerson
How do I search for more than one email domain? Do I use a case statement, if so how?
View 2 Replies
View Related
Feb 14, 2008
Hello,
I forget. How can PATINDEX be used to find column values where the data is all numeric? I've tried, the following:
select distinct acct from tbl_CYProcessedSales
where PatIndex('%[0-9]%',Acct) > 0
order by acct
Acct is varchar(8). What am I doing wrong?
Thank you for your help!
CSDunn
View 12 Replies
View Related
Dec 6, 2007
I am creating a view to pull data for a UPS integration I am doing. I currently have this view where I pull my data from. All is well accept for my PATINDEX,
Currently I have this PATINDEX So when a user puts this into delivery instructions "#999999" UPS_FINAL returns 999999 which is good for me to use. But The PATINDEX will also grab all kinds of neat stuff out of the delivery instructions field when I really only want whatever is after the # sign in the field.
I am new to sql and dont quite understand how this search is working but I think I am in need of a better way to search the field
Code:
WHEN SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) IS NULL
THEN dbo.Address_Table.ups_code
ELSE SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) END AS UPS_FINAL
-------------------------------------------------------------------------------------
What I am looking to accomplish is a SUBSTRING that will search delivery_instructions
Code:
SELECT
TOP (100) PERCENT P21PLAY.dbo.p21_view_oe_pick_ticket.pick_ticket_no,
P21PLAY.dbo.p21_view_oe_hdr.order_no, P21PLAY.dbo.p21_view_oe_hdr.customer_id,
P21PLAY.dbo.p21_view_oe_hdr.ship2_name, P21PLAY.dbo.p21_view_oe_hdr.ship2_add1,
P21PLAY.dbo.p21_view_oe_hdr.ship2_add2,
P21PLAY.dbo.p21_view_oe_hdr.ship2_city,
P21PLAY.dbo.p21_view_oe_hdr.ship2_state,
P21PLAY.dbo.p21_view_oe_hdr.ship2_zip,
P21PLAY.dbo.p21_view_oe_hdr.po_no,
P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id AS Carrier,
P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id AS Supplier,
P21PLAY.dbo.p21_view_oe_hdr.cod_flag,
P21PLAY.dbo.p21_view_oe_hdr.terms,
P21PLAY.dbo.p21_view_oe_hdr.ship2_country,
P21PLAY.dbo.p21_view_oe_hdr.ship_to_phone,
P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
dbo.Address_Table.ups_code,
-----------Looks for special character and returns next 6 spaces as UPS_Shipper----------
SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%',
P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6)
AS UPS_Shipper,
------------------Checks view for email address or assigns alternate------------------
(CASE WHEN charindex('@', p21_view_contacts.email_address) > 0 THEN p21_view_contacts.email_address ELSE
'email@domain.com' END) AS alternate_address,
'Y' AS QVN, 'email@domain.com' AS failureaddress,
P21PLAY.dbo.p21_view_contacts.email_address,
------------When carrier_id is not one of these # then Null; else ------------------------------
CASE WHEN P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id NOT IN (105188, 105191, 105194, 105197, 105200,
105203, 105206, 105209, 105212) THEN NULL
----------------- Looks for special Character in delivery_instructions; if NULL then ups_code;
ELSE return value from delivery_instructions as UPS_Final--------------------
WHEN SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) IS NULL
THEN dbo.Address_Table.ups_code
ELSE SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) END AS UPS_FINAL
FROM dbo.Address_Table INNER JOIN
P21PLAY.dbo.p21_view_oe_pick_ticket INNER JOIN
P21PLAY.dbo.p21_view_oe_hdr ON P21PLAY.dbo.p21_view_oe_pick_ticket.order_no =
P21PLAY.dbo.p21_view_oe_hdr.order_no ON
dbo.Address_Table.id = P21PLAY.dbo.p21_view_oe_hdr.customer_id LEFT OUTER JOIN
P21PLAY.dbo.p21_view_contacts ON P21PLAY.dbo.p21_view_oe_hdr.contact_id = P21PLAY.dbo.p21_view_contacts.id
WHERE
(P21PLAY.dbo.p21_view_oe_hdr.completed <> 'Y')
AND (P21PLAY.dbo.p21_view_oe_hdr.delete_flag <> 'Y')
AND (P21PLAY.dbo.p21_view_oe_hdr.will_call <> 'Y')
ORDER BY P21PLAY.dbo.p21_view_oe_pick_ticket.pick_ticket_no
Hope this makes since
View 4 Replies
View Related
Sep 5, 2007
---Checks that input only contains numbers
if PatIndex('%[^0-9]%','11') > 0
Begin
Print 'Not all numbers'
End
Else
Begin
Print 'All numbers'
End
---Checks that input only contains letters
if PatIndex('%[^a-z]%','aaaaa') > 0
Begin
Print 'Not all letters'
End
Else
Begin
Print 'All letters'
End
--Checking for mixed input
If PatIndex('%[^0-9][0-9]%','abc') > 0
Begin
Print 'Alpha numeric data'
End
Else
Begin
Print 'Either all numbers or all letters'
End
--Checks that value must start with a letter and a number
If PatIndex('[^0-9][0-9]%','A1anamwar11') > 0
Begin
Print 'Starts with a letter and a number'
End
Else
Begin
Print 'Does not start with a letter and a number'
End
--Checks that value must End with a letter and a number
If PatIndex('%[^0-9][0-9]','A1anamwar11a1') > 0
Begin
Print 'Ends with a letter and a number'
End
Else
Begin
Print 'Does not End with a letter and a number'
End
--Checks that value must Start with a letter and Ends with a number
If PatIndex('[^0-9]%[0-9]','namwar1') > 0
Begin
Print 'Starts with a letter and ends with a number'
End
Else
Begin
Print 'Does not start with a letter and ends with a number'
End
Namwar
<Link removed by georgev>
View 13 Replies
View Related
Oct 30, 2007
Hi All,
Can we use the SUBSTRING or PATINDEX function with a User Defined Function as one of the parameters? Something like this:
Code Block
Select SUBSTRING(Select * from DB.dbo.Function('Parameter'), PATINDEX('%\%', Select * from DB.dbo.Function('Parameter'), 8)
Mannu.
View 1 Replies
View Related
Oct 30, 2007
Hello,
I understand that it is not possible to use PATINDEX in a Derived Column transform. I'm trying to eliminate leading zeros from a column where the string is always 14 characters long, but the first non zero character could occur any number of characters from the left of the string. The following achieves what I need:
substring([Account No w/0s], patindex('%[^0]%', [Account No w/0s]), 14) AS ExtractedAcctNo
Does anyone happen to know how I might express this differently in a Derived Column transform? Should I consider a calling a function through an OLE DB Command transform on each row instead? Maybe I should have a SQL Task that runs an UPDATE statement against the column.
Thank you for your help!
cdun2
View 6 Replies
View Related
Aug 21, 2007
Hi,
My database is not case-sensitive, but I want output like...
SELECT patindex('%[A-Z]%','gaurang Ahmedabad')
The output should be first occurrence of uppercase A to Z, so output should be 9 it should not be 1.
Above query is giving output as 1 bcoz the 1st character in the expression is 'g' and it is in A to Z, but this is not capital 'G'. The 1st capital letter in the expression is 'A' (9th character in the expression).
Is there anyway to achieve this using PATINDEX? or Is there any other way to achieve this?
Thanks,
Gaurang Majithiya
View 7 Replies
View Related
Jul 17, 2015
I would like solving the following issue using the Patindex function i cannot retrieve or extract the single numeric value as an example in the the values below i would like retrieve the Value 2, but in my result set the value 22 also appears or it is completely omitted.
"2 8 7"
"2 8"
"2"
"22"
"3 2 8"
I have tried the following
Patindex('%[2]% [^0-9] [^1] [^3] [^4] [^5] [^6] [^7] [^8] [^9]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col3,
Patindex('[2]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col4,
Patindex('%[2][^0-9]%',Replace(Marketing_Special_Attributes,'^',' ')),
View 5 Replies
View Related
Mar 3, 2006
hi
I had a view in which I did something like this
isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
Thanks in advance.
View 5 Replies
View Related
Jan 7, 2004
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)
Try
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = "done"
cn.Close()
Catch ex As Exception
Label1.Text = ex.Message
End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Please I would appreciate any advice...
View 2 Replies
View Related
Feb 23, 2008
Original suggestion for my problem was - Select * from TableA where ID not in ( Select ID from TableB)
When I run the query below using the IN operator I get 227 records returned but when I use NOT IN I get zero records when I expect well over 10,000. What am I missing?
using SQL 2000 server
SELECT LinksInfo.L_ID, LinksInfo.C_ID, Companies.C_CompanyName, Companies.C_Email, Companies.C_CompanyEmailFROM LinksInfo INNER JOIN Companies ON LinksInfo.C_ID = Companies.C_IDWHERE (LinksInfo.L_ID IN (SELECT ZL_ID FROM Location_Zip))
ZL_ID is not a primary key in Location_Zip
View 2 Replies
View Related
Mar 25, 2008
Hello again,
I think im missing something here, i just cant find out what it is.
I have a temp table:
CREATE TABLE #tempSearch(tempID BIGINT IDENTITY(1,1) PRIMARY KEY,username NVARCHAR(20) COLLATE Finnish_Swedish_CI_AS,lastlogin DATETIME,signupdate DATETIME)
Now i am trying to retrieve some data for each user that is inside this tempSearch list and have an id over xxx (xxx = the value of the parameter @first_id):
SELECT @sql = 'SELECT profile_publicinfo.username, profile_publicinfo.gender, profile_publicinfo.signupdate, profile_profilephoto.imageurl, profile_profilephoto.alttext, settings_username.color, profile_publicinfo.lastloginFROM #tempSearch INNER JOIN dbo.profile_publicinfo ON profile_publicinfo.username = #tempSearch.usernameINNER JOIN dbo.settings_privateinfo ON settings_privateinfo.username = profile_publicinfo.usernameFULL OUTER JOIN dbo.profile_coolfacts ON profile_coolfacts.username = profile_publicinfo.username FULL OUTER JOIN dbo.profile_profilephoto ON profile_profilephoto.username = profile_publicinfo.usernameFULL OUTER JOIN dbo.settings_username ON settings_username.username = profile_publicinfo.usernameWHERE (profile_publicinfo.username IN (SELECT username FROM #tempSearch))AND #tempSearch.tempID >= @first_id'
SELECT @paramlist = '@first_id int'EXEC sp_executesql @sql, @paramlist, @first_id
I need to get the tempID from the tempSearch table in order to compare it with @first_id
When i run this i get the same username repeated like 30 times then it moves over to the next, when i debug the #tempSearch it looks fine, just the users that are suppose to be there.
View 1 Replies
View Related
Jun 17, 2004
Hi
I think I have installed MSDE sucessfully. The new servie is running, but how can I test ifit is working? Can I place my files anywhere on the system?
All help appreciated
View 1 Replies
View Related
Apr 25, 2006
I am using the following C#...
SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class loader = new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();
loader.ConnectionString = "PROVIDER=SQLOLEDB; Server=(local); database=TestDB; user id=test; password=password";
loader.ErrorLogFile = "C:log.txt";
loader.SchemaGen = true;
loader.SGDropTables = true;
loader.CheckConstraints = true;
loader.Execute(
"C:xsd.xsd"/* this file below */,
"C:xml.xml"
);
To generate tables in the database using the following xml schema...
(file: xsd.xsd...)
My problems is that it doesn't generate the tables and I am following the SQLXML2.0 documentation as closely as possible.
Does anyone know what I have to do to get it to generate the tables? Running it doesn't even produce an error.
Cheers,
Matt.
View 1 Replies
View Related
May 3, 2002
OK so I'm using dynamic sql to enable me to have a variable for the order by, but now it's giving me the following error
Syntax error converting character string to smalldatetime data type.
Any help is very much appreciated
Code follows
CREATE PROCEDURE spBattingAve1
@cid datetime,
@cid1 datetime,
@sid nvarchar(10)
AS
DECLARE @query nvarchar(4000)
SET @query = 'SELECT MAX(t_batting.runs) AS BestRuns,
COUNT(t_batting.dnb) AS DidNotBat, t_player.surname, t_player.firstname,
t_batting.player_id,
Sum(t_batting.runs) as SumOfruns, COUNT (*) AS Games,
COUNT (t_batting.notout) as nout,
(Sum(runs)/(COUNT(*)-(COUNT(notout)+COUNT(dnb)))) as AverageRun,
(COUNT(*)-COUNT(dnb)) AS Innings,
(SELECT COUNT(*) FROM t_batting inner join t_game on t_batting.game_id = t_game.game_id WHERE (t_batting.player_id = t_player.player_id) AND runs >= 50 AND runs <100 and t_game.date >= '+@cid+' And t_game.date <= '+@cid1+' GROUP BY t_batting.player_id) AS Overfifty,
(SELECT COUNT(*) FROM t_batting inner join t_game on t_batting.game_id = t_game.game_id WHERE (t_batting.player_id = t_player.player_id) AND runs >= 100 and t_game.date >= '+@cid+' And t_game.date <= '+@cid1+' GROUP BY t_batting.player_id) AS Overton
FROM t_batting
INNER JOIN t_game ON t_batting.game_id = t_game.game_id
INNER JOIN t_player ON t_batting.player_id = t_player.player_id
WHERE t_batting.player_id = t_player.player_id
AND t_game.date >= '+@cid+' And t_game.date <= '+@cid1+'
GROUP BY t_batting.player_id, t_player.surname, t_player.firstname, t_player.player_id
HAVING (COUNT(*)-(COUNT(notout)+COUNT(dnb))) <> 0
order by '+@sid+'
DESC'
exec (@query)
View 1 Replies
View Related
Jun 21, 2001
In SQL 7.0, when i use the below query in query analyzer, I got an error message. why?
======================
Set Identity_insert on
UPDATE Table1
SET no = 3
WHERE no = 4
Set Identity_insert off
go
Error message
=============
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'off'.
View 2 Replies
View Related
May 1, 2006
Hi,
I am on a box with 4 GB of memory that only runs SQL Server. SQL Server is using 1.7 GB as per task manager. There are a lot of databases on this box. I looked at the boot.ini and /3GB was not set. I have done this at least 50 times before: turn on the /3GB switch and SQL Server will use up to 2.7 GB per task manager. (I know it is actually 2GB and 3GB but it seems that in task manager it shows up as 1.7-1.8 and 2.7-2.8)
Anyway back to the story. So I set /3GB, rebooted the server and SQL Server still only consumes up to 1.7GB. I realize that SQL Server might not actually need more memory, but I have a distinct feeling that it wants more but is being constrained; that is, the the /3GB switch is not working. More relevant information:
1) the boot.ini line:
multi(0)disk(0)rdisk(0)partition(1)WINNT="Microsoft Windows 2000 Advanced Server" /fastdetect /3GB
2) max server memory (MB) = 3000
3) min server memory (MB) = 3000
4) awe enabled = 0
5) @@version = 8.00.2039 which is SQL Server 2000 SP4
Has anyone seen this before? Any ideas on how to troubleshoot this?
View 1 Replies
View Related
Oct 16, 2006
Hi all:
Because me not this functioning the following one query.
simply me not the function respects min, because?, that I am doing badly?
Select Distinct(Min(c.gentime)),
a.CardNumber,a.CardHolderId,a.Deleted,
b.RecordId,b.FirstName,b.LastName,b.Deleted,b.Note 4,
c.param3,c.param2,c.param1,c.recvtime,c.gentime,c. link1,c.link2,c.link3,c.deleted,c.recordid,c.seqid
From Card a,
CardHolder b,
History c
Where ((a.cardholderid = b.recordid)And(b.recordid=c.link3))
And(a.Deleted = 0)And (b.Deleted = 0)And(c.deleted = 0)
And(a.cardnumber Between 1500 And 1600)
And(b.note4 <> 'Mantenimiento')
And(c.RecvTime >= CONVERT(DATETIME, '2006-10-01 00:00:00', 102))And(c.RecvTime <= CONVERT(DATETIME, '2006-10-12 23:59:59', 102))
And(c.Link1=10)
Group By a.CardNumber, a.CardHolderID, a.Deleted,
b.RecordID, b.Deleted, b.FirstName, b.LastName, b.Note4,
c.Deleted, c.RecvTime, c.GenTime, c.Param1, c.Param2, c.Param3, c.Link1, c.Link2, c.Link3, c.Link4,c.recordid,c.seqid
Order By a.CardNumber,c.recvtime
Thanks.:confused:
View 6 Replies
View Related
Mar 2, 2006
hi..why this SP is not working that giving me error like
Server: Msg 2714, Level 16, State 1, Procedure test_E, Line 12
There is already an object named '#emp' in the database.
my one of the developer used this every where... what should i do if i dont wanna to change any of the code...i wanna some how work this SP...
create proc test_E
as
select * into #emp
from (select * from emp) p
select * from #emp
drop table #emp
select * into #emp
from (select * from dept) p
select * from #emp
drop table #emp
T.I.A
Papillon
View 5 Replies
View Related
Apr 11, 2006
select replace(local_notes,char(13),' ') from locals
Not Working ......
View 12 Replies
View Related
Sep 20, 2006
declare @table table(ad_num varchar(20), ad_str1 varchar(20))
insert @table
select '20', '20 apple avenue' union all
select '20', 'apple avenue'
select * from @table
if (select * from @table where substring(ad_str1, 1, 1) like '[0-9]'))
print 'address starts with a number'
else
print 'address starts with a letter'
i want to write a code that prints a msg if the string starts with a number else print another msg.
View 20 Replies
View Related
Sep 26, 2007
In SQL2000 Enterprise manager, there is always an indication of WHICH server & database an object is opened from when it has focus.
So when I'm working on the same table in test and production servers I can tell at a glance what it belongs to.
I cannot seem to duplicate this in any way in SQL Manager 2005 Express which could lead to all sorts of trouble and really slows me down.
Have I missed something really simple in setup or use of the product?
TIA,
Richard
View 2 Replies
View Related
