|
Hi
I would like to share cool feature of SQL Server with you guys like:
Please use the following query when concatenate two or more rows with comma (or any you want) in a single string.
SELECT STUFF((SELECT ',' + RTRIM(RegionDescription) FROM Region FOR XML PATH('')),1,1,'') AS 'Regions'
Please do let me know, if you have any doubt.
Please provide "Vote" if this would be helpful.
Thanks,
Imdadhusen
sunaSaRa Imdadhusen
+91 99095 44184
+91 02767 284464
|
|
|
|
|
Good stuff.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Publish it as a tip/trick, that way people who are looking for it will find it. Your main audience here are people looking to help others and probably already know this method.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your suggestions, i will be share this on Tips/Tricks sections
sunaSaRa Imdadhusen
+91 99095 44184
+91 02767 284464
|
|
|
|
|
Hi There,
I don't really use sql so this can be an stupid questoin but, say ive got a column called accountNumber with the following values:
55123
55124
55124
...etc
and i want to change all the values (5000+ entries) to a concatenation of '000' + the current accountNumber. i.e. 00055123;00055124;00055125
how will i achieve this?
Regards and thanks
|
|
|
|
|
here it is
update tablename set accountNumber = '000'+accountNumber
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
What data type is accountNumber stored in? What database are you using?
|
|
|
|
|
Data type is string and it is a sql express database.
Also, i cannot update the etries that already have a '000' prefix so i can do something like :
UPDATE TableName
SET AccountNumber = '000' + AccountNumber
WHERE AccountNumber NOT LIKE '000%'
Correct?
|
|
|
|
|
What you need to do is use the LPAD function in SQL Server to pad the number to a set size. So, suppose you want to have it padded out to 10 characters, you'd use
UPDATE TableName SET AccountNumber = LPAD(AccountNumber, 10, '0')
|
|
|
|
|
Hello,
I have this structure :
<br />
CREATE PROCEDURE external_sp<br />
AS<br />
BEGIN<br />
<br />
BEGIN TRANSACTION<br />
EXEC internal_sp @param1,@result out<br />
<br />
if( @result <> 1 )<br />
BEGIN<br />
COMMIT TRANSACTION<br />
END<br />
ELSE<br />
BEGIN<br />
ROLLBACK TRANSACTION<br />
END<br />
<br />
END<br />
The problem : inside the stored procedure "internal_sp" there is another BEGIN TRANSACTION block.
If this internal transaction block get rolled back, the outer transaction (in the external_sp stored procedure) fails.
How can i solve this issue ??
Any help will be appreciated...
|
|
|
|
|
Hello,
If you use internal transaction you don't need to use another
transactions.
|
|
|
|
|
?! And if i really need to nest transactions ??
|
|
|
|
|
HI,
I am using SQL SERVER 2005.
I need to find what is the best performance method for my 3 sql Data adapters.
first method:-
1) Open sql connection
2) Initialise 1st data adapter and fill for the data table 1.
3) Initialise 2nd data adapter and fill for the data table 2.
4) Initialise 3rd data adapter and fill for the data table 3.
5) Process my work
6) close connection.
second method:-
a) 1. Open sql connection
2. Initialise 1st data adapter and fill for the data table 1.
3. close connection.
b) 1. Open sql connection
2. Initialise 2nd data adapter and fill for the data table 2.
3. close connection.
c) 1. Open sql connection
2. Initialise 3rd data adapter and fill for the data table 3.
3. close connection.
d) 1. Use thread concept for a) ,b) and c) and take results for adapters.
2. process my work.
so can any one tell me what is the best performance method?
first method or second method?
Thanks
|
|
|
|
|
third method:-
2) Initialise 1st data adapter
3) Initialise 2nd data adapter
4) Initialise 3rd data adapter
1) Open sql connection
2) fill for the data table 1.
3) fill for the data table 2.
4) fill for the data table 3.
6) close connection.
5) Process my work
As to your second method... you do know that the DataAdapter will handle the open/close for you don't you?
Better yet, don't use DataAdapters, but that's another subject.
P.S. And why use separate ones rather than only one?
|
|
|
|
|
hi
my server ip address is changed,should i configure the new ip address with the sqlserver 2000 which i m using in that system.
if so, then wt are the steps to configure ip address to sqlserver 2000
Thanks in advance
Vijay Kumar D
|
|
|
|
|
Hmm 2000 is a long time ago but I'd try this, attempt to connect using your existing IP address, when that does not work think about your question. If you are using a named server you may not need to change the connection information.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I want to verify user credentials from login form and check it in sql server Database through stored procedure.
And Confirmation sen back to front end.based on that only i'll allow the user.
Thanks & regards
kiran
|
|
|
|
|
The google force is weak in you.
Have a look here.[^]
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Hi,
I am using VS2008, and Sqlexpress2005; before uninstall VS everything works fine. I reinstall VS 2008 and install SQLEXpress 2005; then I tried to add my databse which got this error;however, I went through many articles on the internet but still the same problem. do you have any idea?
its the error
"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"<br />
|
|
|
|
|
|
Thanks for the help!
Im a network guy (cisco)/Server admin and I would really like to become proficient with SQL.
I have a very VERY basic grasp of it, so if you could share some possible resources, and maybe some study material links that would be great.
If there is anything with practice labs that would be great. I all ready have a server setup with 2008 ready to be sacrificed.
Thanks for any incite!
Andy
|
|
|
|
|
This site has a quick reference guide to SQL. It has definitions, examples and some samples to try out: http://www.w3schools.com/sql/default.asp
Picking up a book like "SQL: The Complete Reference" could help a lot too. It's packed with information, but you can kind of pick and choose if there are certain areas that you are focusing on. I know they have multiple editions out for it, but I haven't checked out the most recent edition.
I agree that rolling up your sleeves and digging into tutorials and labs is the way to go though. Good luck!
|
|
|
|
|
Buy a copy of Sql Server 2008 T-SQL Fundamentals by Itzik Ben-Gan published by Microsoft; It will give you a thorough grounding in T-SQL. I can't recommend this book highly enough.
|
|
|
|
|
These are all wonderful, thank you so much for the valuable incite!
|
|
|
|
|
Hi!
I'm using SQLite for my game. When a particular button is clicked,I've to get the selection of a list control and delete it from the list control and from the database. for this I'm using the following code and query.
if(m_pSelectProfileImage->getRelativePosition().isPointInside(m_MousePos))
{
stringw strtoDelete = profileList->getListItem(profileList->getSelected());
strtoDelete.make_lower();
strtoDelete.trim();
if(!strtoDelete.equals_ignore_case(L"default"))
{
irr::core::stringc testStr = "DELETE FROM profile WHERE name = '";
testStr += strtoDelete;
testStr += "'";
pManager->SQLdb.Query(testStr);
profileList->removeItem(item);
}
}
I execute the above line and open the database file using SQLite Database Browser to check whether the particular record has been deleted. It's not deleted. When I put a break point to verify the query, my list selection is there in the query(testStr). What might be the problem? How to delete a record?
|
|
|
|