|
Hi all,
I am in a fix for which i really need some input with this problem
colA-------- --COLB
1000-------- --,,,--EXCESS OF ,,,
1000A------- --A,,
100AB------- --A,B,,
100ABC------ --A,B,C,
This result was derived to achive the table Below
DESIRED RESULT
colA-------- --COLB
1000-------- --NULL
1000A------- --A
100AB------- --A
100AB------- --B
100ABC------ --A
100ABC------ --B
100ABC------ --C
BUT THIS IS THE RESULT I GET
colA-------- --COLB
1000-------- --NULL
1000A------- --A
1000A------- --NULL
1000AB------ ---A
1000AB------ ---B
1000AB------ --NULL
100ABC------ --A
100ABC------ --B
100ABC------ --C
THIS IS BECAUSE THE FIRST RESULT CONTAINS ESCESS OS ,,, I WANT TO
REMOVE THOSE AND HAVE ONLY THE VALID LETTERS,
IS THERE A WAY TO REMOVE ONLY THE EXCESS COMMAS
ANY INPUT BE VERY HELPFUL
THANKS
SQL DEVELOPER
|
|
|
|
|
I recommend a regular expression that looks for 1 or more contiguous commas and replaces with a single comma. I can't give you the regex straight away, but maybe you can find one on a regex site somewhere.
|
|
|
|
|
I am using MS Access to make a simple database and i used a lookup field to another table so that the user can update the lookup as needed. But when i copy the records to another table i want them to copy as text data not lookup data. I would like to know how to copy it to another table and get the actual value instead of the foriegn key.
|
|
|
|
|
How can I get more control over generating SQL scripts? I would like to optionally include drops where SSMS only allows drops OR creates etc. I would like to script as alter not create etc.
|
|
|
|
|
Perhaps Red Gate SQL Compare? We use it for generating scripts to update a schema, rather than drop/recreate.
|
|
|
|
|
Hai,
I have Windows XP SP2 and I have installed SQL Server 2005. while i'm opening SQL sever Management studio i get a pop message indicating that "SSPI Error Context". Can anyone sort out this problem.
aanryn
|
|
|
|
|
Hello!
I am trying to retirve data from almost five tables simultaneously, but having problems in use of a single query!!!
Here is the sample of thouse five tables
students: stuid, classID, sectionID, termID, sessionID, stuName, other fields
Classes: classID, className
Sections: sectionID, sectionName
Terms: termID, termName
Sessions: sessionID, sessionName
NOw if i want to extract data with a single query in somewhat this form
stuName, className, sectionName, termName, sessionName
Can someone help me in this, as to how the best query can be formed?
thank you!!
-- modified at 1:28 Monday 30th July, 2007
|
|
|
|
|
SELECT stuName = s.stuName,
className = c.className,
sectionName = sc.sectionName,
termName = t.termName,
sessionName = ss.sessionName
FROM students s
LEFT OUTER JOIN Classes c ON s.classID = c.classID
LEFT OUTER JOIN Sections sc ON s.sectionID = sc.sectionID
LEFT OUTER JOIN Terms t ON s.termID = t.termID
LEFT OUTER JOIN Sessions ss ON s.sessionID = ss.sessionID
Eliz.K
<awww.oin1.com< a="">
|
|
|
|
|
Thank You Eliz!
One thing more, also if i want to get the record of a single student, can i used the where clause after joins like
where s.stuid = '2222'
???
thanks!!!
|
|
|
|
|
|
I am calling a store procedure on sql server 2000 table as below::
return SqlHelper.ExecuteDataset(cn, "abc",54);
It is giving the following exception...
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding error.
can anyone suggest me the solution plz..
hiiiiii
|
|
|
|
|
hope procedure execution is take more time.
set connectiontimeout property of connection object (cn)
Regards
KP
|
|
|
|
|
Thanks,
But it is not working. can you tell me the way to attach timeout with sqlhelper??
hiiiiii
|
|
|
|
|
Your problem is not with the connectiontimeout. It is the command timeout. Somewhere in your sqlhelper there should be a command timeout. The default is 30 seconds. You will need to increase it to something larger. You may also want to look at your sql statements and consider creating indexes to help with the speed.
Hope that helps.
Ben
|
|
|
|
|
Increase your timeout time and/or profile your stored procedure and see what is taking up time.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
I want to use stored procedure in place of select statement for cursor.
e.g.
DECLARE ProfInd_CURSOR CURSOR for
dbo.EM_GET_WORKHOURS 88,1,'01/01/2007','08/08/2007'
OPEN ProfInd_CURSOR
..............................
any idea
Do good and have good.
|
|
|
|
|
Modest Bird wrote: I want to use stored procedure in place of select statement for cursor.
I don't think that you can do this. What you can do is create a temporary table in your current procedure and fill it from nested procedure. select from this temporary table in cursor
|
|
|
|
|
Hello Modest,
You can try to use User Definied Function for your cursor instead of stored procedure.
Like this:
DECLARE cur CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT * FROM dbo.fn_GetTestData(1)
Please get the sample from --> http://www.oin1.com/Technical/SQL/FunctionInCursor.htm[^]
Eliz.K
|
|
|
|
|
You can't. You have to provide a SELECT statement, and you cannot directly SELECT from a stored procedure's results.
You can replace the stored procedure with a user-defined function - either an inline table-valued function, which simply contains a single SELECT statement, or a multistatement table-valued function.
Alternatively, the INSERT statement can insert the results of a stored procedure into a table (e.g. INSERT table (columns) EXECUTE stored_proc). You would need to create a temporary table or table variable to store the results, with the appropriate schema, insert into the temporary table/table variable, then declare the cursor to select from that.
|
|
|
|
|
what string function or any expression that returns the first letter of each word in uppercase, all other letters in lowercase in mssql2000?
thank you in advance!!!
jyn
|
|
|
|
|
Not sure if there is any built-in function from MSSQL. But you can write your own function -->
select UPPER(SUBSTRING(colName, 1, 1)) + LOWER(SUBSTRING(colName, 2, LEN(colName))) from tblTable
Eliz.K
www.oin1.com
|
|
|
|
|
this does only first letter of of the text to uppercase.
not first letter of all words in the text
there is no in built function for this. need to write a udf for this.
Regards
KP
|
|
|
|
|
Oh ya... sorry for my mistake.
To look through every word will affect the performace. I have a way which is not very clever, but should be workable.
Write a user definied function, where you replace ' a' to ' A', ' b' to ' B'.. and so on.
Sorry...
Eliz.K
www.oin1.com
|
|
|
|
|
Hi guys
I am trying to execute a dts package which has a text file as it's source (comma delimited with .txt extension) and then loads it into a table in sql server 2000. When I execute it, it fails on the first datarow it reads on the second field, the first field's value it reads.
THis is the error message:
"The number of failing rows exceeds the maximum specified.
Readfile transform 'DTSTransformation_2', source column 'MSISDN' (DBTyPE_STR), destination column 'PhoneNo'
(DBTYPE_STR): File '0842473397' not found."
funny thing is that '0842473397' is not a file, it's the second value in the first data row in the text source file.
Baffled, Plz Help.
Kiefie
Jack of all trades, master of some.
|
|
|
|
|
Nevermind I got it, I should've used 'copy column' and not 'read file' transformation.
Kiefie
The man with a plan.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
