Concatenate strings from different rows

Hello.

I have a table like:

Id Name Description OrderId
1 Microsoft This is a 0
1 Microsoft huge company. 1

I need to create a select query that will concatenate description for both entries and output it like

Microsoft - This is a huge company.

Try using the USE XML PATH () as part of the select statement; maybe something like:

Code Snippet

declare @.testData table
( id integer,
[Name] varchar(10),
Description varchar(15),
orderId integer
)
insert into @.testData
select 1, 'Microsoft', 'This is a', 0 union all
select 1, 'Microsoft', 'huge company.', 1

select distinct
[Name] + ' - ' +
( select description + ' ' as [text()]
from @.testData b
where a.id = b.id
order by orderId
for xml path ('')
) as outputString
from @.testData a

/*
outputString
--
Microsoft - This is a huge company.
*/

Concatenate nuimbers

I have two fields that have numbers company and employee, I want to
concatenate these numbers so it it takes the comapny number (9000) and
employee number (116258) and makes 9000116258.
When i use this query
(select company+employee as uid from cr_staging) it adds the numbers.
I also tried
SELECT Company, Employee, Company & ' ' & Employee AS uid
FROM cr_staging
but it gives me an error invalid operator for data type.
Is there any suggestions on this problem?
Thanks,
This will work:
select CONVERT(varchar(10),company) + convert(varchar(10),employee) from
cr_staging
"Eric" wrote:

> I have two fields that have numbers company and employee, I want to
> concatenate these numbers so it it takes the comapny number (9000) and
> employee number (116258) and makes 9000116258.
> When i use this query
> (select company+employee as uid from cr_staging) it adds the numbers.
> I also tried
> SELECT Company, Employee, Company & ' ' & Employee AS uid
> FROM cr_staging
> but it gives me an error invalid operator for data type.
> Is there any suggestions on this problem?
>
> Thanks,
>
|||+ adds numbers when they are numbers (int, numeric...)
+ combines varchar data
You need to convert the columns to varchar before you "add" them.
SELECT CONVERT(varchar(20),company) + CONVERT(varchar(20),employee)
FROM cr_staging
Keith
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:9862CFBF-3B7A-43B0-9A33-136F2B228A05@.microsoft.com...
> I have two fields that have numbers company and employee, I want to
> concatenate these numbers so it it takes the comapny number (9000) and
> employee number (116258) and makes 9000116258.
> When i use this query
> (select company+employee as uid from cr_staging) it adds the numbers.
> I also tried
> SELECT Company, Employee, Company & ' ' & Employee AS uid
> FROM cr_staging
> but it gives me an error invalid operator for data type.
> Is there any suggestions on this problem?
>
> Thanks,
>
|||Assuming that your fields are defined as INT and that you want to keep the
same number of digits (4 + 6), try this:
select
(
RIGHT('0000' + CAST(Company AS varchar(4)), 4)
+
RIGHT('000000' + CAST(Employee AS varchar(6)), 6)
)
as UID
from cr_staging
This will give you the result: '9000116258'
HTH,
Robert

Concatenate nuimbers

I have two fields that have numbers company and employee, I want to
concatenate these numbers so it it takes the comapny number (9000) and
employee number (116258) and makes 9000116258.
When i use this query
(select company+employee as uid from cr_staging) it adds the numbers.
I also tried
SELECT Company, Employee, Company & ' ' & Employee AS uid
FROM cr_staging
but it gives me an error invalid operator for data type.
Is there any suggestions on this problem?
Thanks,This will work:
select CONVERT(varchar(10),company) + convert(varchar(10),employee) from
cr_staging
"Eric" wrote:
> I have two fields that have numbers company and employee, I want to
> concatenate these numbers so it it takes the comapny number (9000) and
> employee number (116258) and makes 9000116258.
> When i use this query
> (select company+employee as uid from cr_staging) it adds the numbers.
> I also tried
> SELECT Company, Employee, Company & ' ' & Employee AS uid
> FROM cr_staging
> but it gives me an error invalid operator for data type.
> Is there any suggestions on this problem?
>
> Thanks,
>|||+ adds numbers when they are numbers (int, numeric...)
+ combines varchar data
You need to convert the columns to varchar before you "add" them.
SELECT CONVERT(varchar(20),company) + CONVERT(varchar(20),employee)
FROM cr_staging
--
Keith
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:9862CFBF-3B7A-43B0-9A33-136F2B228A05@.microsoft.com...
> I have two fields that have numbers company and employee, I want to
> concatenate these numbers so it it takes the comapny number (9000) and
> employee number (116258) and makes 9000116258.
> When i use this query
> (select company+employee as uid from cr_staging) it adds the numbers.
> I also tried
> SELECT Company, Employee, Company & ' ' & Employee AS uid
> FROM cr_staging
> but it gives me an error invalid operator for data type.
> Is there any suggestions on this problem?
>
> Thanks,
>|||Assuming that your fields are defined as INT and that you want to keep the
same number of digits (4 + 6), try this:
select
(
RIGHT('0000' + CAST(Company AS varchar(4)), 4)
+
RIGHT('000000' + CAST(Employee AS varchar(6)), 6)
)
as UID
from cr_staging
This will give you the result: '9000116258'
HTH,
Robert

Complicated question about update procedures

My company works with a fairly large database that needs to be kept live.
My problem is that we need a method of updating the database without messing the data, or crashing the website.
I am sure this is a common problem with a number of solutions. Couldanyone please direct me to a good article on the best practices forupdating databases like this?
What do you mean by updating the database? Just inserting records o chaging its structure?
|||Anything. Inserting records is not a problem. It is changing the structure which is.
When I am developing, I dont want to be working on the live database.
But if I copy the database and edit the copy, what happens to any new data on the live database?
Hope that is not confusing.
|||When you make updates to the structure of the database you should consider whether it affects the behaviour of the application or not. If it doesn′t you can make the update at anytime (But it will be better to schedule it when there is no heavy traffic). If it affects the application, the update should be accompanied with an update in the application. In this case you should schedule a maintainance stop (obviously the users should be adviced with anticipation), and use that time to update everything, the application, the database, configurations, etc. I also work for a huge company with a lot of servers, databases and web applications running, and this is the way we behave.
|||

I was just wondering what the best approach to this kind of thing is.

For example, lets say I have version 1.0 of an application with a db backend. Now, when I start creating version 2, the application and db will change. Do I :

1. Edit the live db making extra care that version 1.0 is not affected by the changes. This has its obvious problems - one mistake and the whole application goes down.

2. Create a copy of the db, edit that, and then link that in with version 2 when it is ready. The problem with this is that, between the time ot copying the db and releasing version 2.0, the users are still using version 1.0 of the db. I would have to then copy that over. This isnt a problem with minor changes, but it is when I start changing/creating keys and constraints (especially in sql server).
So I was wondering what the industry works around creating the next version of software without corrupting the current version.
I know there is no right answer - it depends on the situation. But any guidence would be appriciated.

|||

jagdipa wrote:

I know there is no right answer - it depends onthe situation. But any guidence would be appriciated.

I don't know that I agree with that statement. I think there is aright answer. It is called the Development, Staging, Productionmodel (DSP). The Staging database starts out as a mirror image ofthe Production database. Update scripts are run and tested on theStaging database until the consistent desiredresult is reached, with the Staging database being restored with abackup of theProduction database before each cycle of tests. This way, whenyou are ready to go live with your changes, you simple run the updatescripts that have been tested and perfected on the Staging databaseagainst your Production database.
Check out these links:
The Development, Staging, and Production Model
Setting up a DSP Environment -- look especially at the Managing Database Development section, and the Staging Environment section


|||Brilliant. Exactly what I was looking for.
I am just wondering if DSP is a standard method the industry uses, and are there other methods applied to this problem?
Again, thank you for the guidance
Jagdip
|||Just wondering if you have any more links about DSP?
|||Hi Jagdip, I was hoping someone else might chime in. I have noidea if the DSP approach is the industry standard, but it's the methodI've come to employ as a best practice for myself (in ideal situationsat least, sometimes some of the steps have been shortcut) and it is theway I've noticed some of my peers have worked. I've only recentlypicked up on the term "DSP" to be honest. When I read thedescription of the acronym I said to myself, "oh, I didn't know therewas a term appplied to this approach".
I just Googled a bit more and came up with this, which uses the DSP approach without using the term:
Migration to Production
I don't have many further resources for you, I'm afraid. The keyis to make your rollout to production consistently repeatable in yourstaging environment. This way when you go live you have minimizedyour risks as much as possible. I can't imagine an approach thatwould be better.


|||The only real problem we have is with updating the database. Writtingthe SQL scripts and saving then is a great way of keeping documentationon DB updates as well as keeping the existing data. Up until now, mycolleage has been working on the live database!!!!
I have had a word with him and he likes it. So thank you for theadvice. I'm in the process of writting and testing a procedure usingDSP, and I will have a look at the other article you gave me.
I guess the only real problem I have with DSP is that it will reallyslow down our RAD ideal. But the boss asked for something like this, sohe's going to have to live with it :-)

Complex queries run slow after SP4 installed

My company's production SQL Server 2000 becomes very slow after SP4 is
installed.
We have figured out that several complex queries which only took 2 seconds
to complete under SP3a now take more than 10 minutes to finish in SP4! The
sympton is similar to the following KB:
http://support.microsoft.com/kb/826906/#XSLTH3120121122120121120120
Has anyone experienced similar problems?
Where can I get the hotfix?
regards
ArthurArthur,
I had the same problem. Product support recommended I install build
2145, but that really didn't help in my case. I wound up re-writing my
queries to not use views -- if you can, I recommend you do the same. Queries
on the base tables are still (mostly) fast.
Regards,
Jonathan
"Arthur" wrote:
> My company's production SQL Server 2000 becomes very slow after SP4 is
> installed.
> We have figured out that several complex queries which only took 2 seconds
> to complete under SP3a now take more than 10 minutes to finish in SP4! The
> sympton is similar to the following KB:
> http://support.microsoft.com/kb/826906/#XSLTH3120121122120121120120
> Has anyone experienced similar problems?
> Where can I get the hotfix?
> regards
> Arthur
>
>|||Dear Jonathan
Thanks for your response.
The bad thing is that over 90% of our stored procedures or queries are based
on views and thus rewritting and testing all of them is just impossible.
Do you think I can fall back to SP3 safely (suppose I have the database
backup before the upgrade)?
regards
Arthur
"Jonathan Levine" <myfoo2@.nospam.nospam> wrote in message
news:C2490F12-DBF9-4854-9F78-06FE833FD131@.microsoft.com...
> Arthur,
> I had the same problem. Product support recommended I install build
> 2145, but that really didn't help in my case. I wound up re-writing my
> queries to not use views -- if you can, I recommend you do the same.
Queries
> on the base tables are still (mostly) fast.
> Regards,
> Jonathan
> "Arthur" wrote:
> > My company's production SQL Server 2000 becomes very slow after SP4 is
> > installed.
> >
> > We have figured out that several complex queries which only took 2
seconds
> > to complete under SP3a now take more than 10 minutes to finish in SP4!
The
> > sympton is similar to the following KB:
> >
> > http://support.microsoft.com/kb/826906/#XSLTH3120121122120121120120
> >
> > Has anyone experienced similar problems?
> >
> > Where can I get the hotfix?
> >
> > regards
> >
> > Arthur
> >
> >
> >|||Arthur,
"Arthur" wrote:
> Do you think I can fall back to SP3 safely (suppose I have the database
> backup before the upgrade)?
If you can roll back, I would do so ASAP. The problem just gets worse
and worse as the queries get longer (I had queries that took 3 or 4 minutes
in SP3a that took several hours in SP4).
-- J|||This may or may not apply to you but we experienced some problems recently
where queries that run fast on my computer were taking forever on the
client's site.
We discovered that SQL Server was reporting an error about parallel queries.
We weren't doing any but we configured the server to only use 1 CPU instead
of "all available processors" and the problem went away.
Good luck
Richard Speiss
"Arthur" <arthurw@.newgroup.nospam> wrote in message
news:u9pGzO9lFHA.2156@.TK2MSFTNGP14.phx.gbl...
> My company's production SQL Server 2000 becomes very slow after SP4 is
> installed.
> We have figured out that several complex queries which only took 2 seconds
> to complete under SP3a now take more than 10 minutes to finish in SP4! The
> sympton is similar to the following KB:
> http://support.microsoft.com/kb/826906/#XSLTH3120121122120121120120
> Has anyone experienced similar problems?
> Where can I get the hotfix?
> regards
> Arthur
>

Complex queries run slow after SP4 installed

My company's production SQL Server 2000 becomes very slow after SP4 is
installed.
We have figured out that several complex queries which only took 2 seconds
to complete under SP3a now take more than 10 minutes to finish in SP4! The
sympton is similar to the following KB:
http://support.microsoft.com/kb/826...122120121120120
Has anyone experienced similar problems?
Where can I get the hotfix?
regards
ArthurArthur,
I had the same problem. Product support recommended I install build
2145, but that really didn't help in my case. I wound up re-writing my
queries to not use views -- if you can, I recommend you do the same. Querie
s
on the base tables are still (mostly) fast.
Regards,
Jonathan
"Arthur" wrote:

> My company's production SQL Server 2000 becomes very slow after SP4 is
> installed.
> We have figured out that several complex queries which only took 2 seconds
> to complete under SP3a now take more than 10 minutes to finish in SP4! The
> sympton is similar to the following KB:
> http://support.microsoft.com/kb/826...122120121120120
> Has anyone experienced similar problems?
> Where can I get the hotfix?
> regards
> Arthur
>
>|||Dear Jonathan
Thanks for your response.
The bad thing is that over 90% of our stored procedures or queries are based
on views and thus rewritting and testing all of them is just impossible.
Do you think I can fall back to SP3 safely (suppose I have the database
backup before the upgrade)?
regards
Arthur
"Jonathan Levine" <myfoo2@.nospam.nospam> wrote in message
news:C2490F12-DBF9-4854-9F78-06FE833FD131@.microsoft.com...
> Arthur,
> I had the same problem. Product support recommended I install build
> 2145, but that really didn't help in my case. I wound up re-writing my
> queries to not use views -- if you can, I recommend you do the same.
Queries[vbcol=seagreen]
> on the base tables are still (mostly) fast.
> Regards,
> Jonathan
> "Arthur" wrote:
>
seconds[vbcol=seagreen]
The[vbcol=seagreen]|||Arthur,
"Arthur" wrote:
> Do you think I can fall back to SP3 safely (suppose I have the database
> backup before the upgrade)?
If you can roll back, I would do so ASAP. The problem just gets worse
and worse as the queries get longer (I had queries that took 3 or 4 minutes
in SP3a that took several hours in SP4).
-- J|||This may or may not apply to you but we experienced some problems recently
where queries that run fast on my computer were taking forever on the
client's site.
We discovered that SQL Server was reporting an error about parallel queries.
We weren't doing any but we configured the server to only use 1 CPU instead
of "all available processors" and the problem went away.
Good luck
Richard Speiss
"Arthur" <arthurw@.newgroup.nospam> wrote in message
news:u9pGzO9lFHA.2156@.TK2MSFTNGP14.phx.gbl...
> My company's production SQL Server 2000 becomes very slow after SP4 is
> installed.
> We have figured out that several complex queries which only took 2 seconds
> to complete under SP3a now take more than 10 minutes to finish in SP4! The
> sympton is similar to the following KB:
> http://support.microsoft.com/kb/826...122120121120120
> Has anyone experienced similar problems?
> Where can I get the hotfix?
> regards
> Arthur
>

Completely Removing SQL Server 2005 Express

I've recently tried to load SQL Server 2005 Express onto the main server that services our small company of 10 people. The server is a W2K Server, and acts as our domain controller. I've had NUMEROUS problems trying to get SQL Server 2005 Express to work on this system - so much so that I've given up trying. However, in attempting to uninstall this, I've now encountered even bigger issues. It seems that the uninstaller leaves files behind (such as VSS Writer, Set up Files) that then will not uninstall themselves. So, being the novice I am, I tried re-installing SQL Server Express as a whole, removing the above files, then uninstalling SQL Server Express "proper." Well, that didn't work either - I got the subordinate stuff to come off, but now the main files won't remove - nor can I reinstall the whole thing any more.

This is an absolute mess - and I need help getting out of it. Can anyone direct me to help on how to SAFELY remove the ENTIRE instance of SQL Server 2005 Express so that nothing else is interrupted/harmed? Any sound advice at this point is most appreciated.

I ve got the same Problem here. Ive installed the Beta of Visual Studie - even after de-installation it was not possible to install Sql Server Developer edition.
I think it is something wrong in registry...|||Not sure about uninstalling, but I think I saw a blurb somewhere on the books online stating you shouldn't install SQL 2005 on a domain controller...|||

SQLWriter and Support files (amongst other 'leftovers') should uninstall just fine after SQL is gone. They may pop up a message saying that removing them may cause other dependent applications to fail. But if you have already removed all instances of SQL Server 2005, you should be fine.

In the situation where you removed them while SQL Express was already on the box, I would suggest installing Express again (to let the installer put back what you deleted), then uninstalling Express, then uninstalling the leftovers.

If that fails, as a last resort you can use the Windows Installer clean up tool. However this may leave some services behind. I definitely recommend

http://download.microsoft.com/download/E/9/D/E9D80355-7AB4-45B8-80E8-983A48D5E1BD/msicuu2.exe

compatibility question

just a question....

my web project is using SQL Express2005 and ASP.NET and C#.
my web hosting company only have MSsql2000. would there be any conflict with regards to my database? im sorry if i sound dumb. im a newbie to this.

thanks a lot!
There will not be a conflict but you may have to make code changes, there are a fair number of features in SQL Express that will not work with SQL 2000. I suggest downloading a copy of MSDE for yourself(which is free) so you can test before going live with the hoster.|||i am only using one mdf file and my asp.net program just deals with the common add, edit, delete transactions. would these transactions differ from version to version of MSSQL?

honestly i did not think there would be a problem coz these transactions are very common. pls correct me if i am wrong.
|||

It depends. Are you using user instances(its in the connection string)? are you using varchar(max), xml types? Are you using the new security features?

Any of the above will prevent the app from working on a sql server 200 machine.

|||

Euan Garden wrote:

It depends. Are you using user instances(its in the connection string)? are you using varchar(max), xml types? Are you using the new security features?

Any of the above will prevent the app from working on a sql server 200 machine.

this is my connection string:
<add name="conn_name" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbase_name.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>

sql2000 doesnt support user instance, does it?

__
i just installed MSDE to make a test. i tried attaching my mdf file (created by SQL Express) to the MSDE but i got this error message:

"An error occured when attaching the database"

i attached that mdf file to the SQL Express and i encountered no problem at all. do you think i will encounter the same problem with my hosting company?

thanks for your time Euan.|||

2 issues here;

1/ MSDE does NOT support user instances so you need to change your conection string to a regular one.

2/ You are trying to attach a SQL2005 db to a SQL2000 machine, this will not work, you will need to move the data and schema onto a SQL Server 2000 implementation.

|||can I implement the membership control (provided by ASP.NET) on MSDE? i can try migrating from SQLEXxpress to MSDE but im not sure if membership control works on that MSDE. sorry if i really sound dumb. im a newbie.|||There is a tool that comes with ASP.Net that supports installation of the databases, aspnet_regsql.exe is its name, check and see if it can install the tables for membership control, I believe it supports SQL 2000 ie MSDE.|||

Euan Garden wrote:

There is a tool that comes with ASP.Net that supports installation of the databases, aspnet_regsql.exe is its name, check and see if it can install the tables for membership control, I believe it supports SQL 2000 ie MSDE.

i just tried it and it installed all the membership tables using this MSDE.
thanks for the help Euan Garden.

Comparioson Reports

Hi There
My client has asked that there existing SQL reports be used for Comparison Reports.
So for example at the moment - they Select a Company site and a department to create a report.
Now they want to have have a another site on the report - like a multipage report in MS Access reports.
Any ideas folks?Reporting services (currently) does not support a multi-select parameter.. I
suspect that will be coming soon..
In the mean time, you may make a parameter which is a text field, and
require users to type in a (comma) delimited list which you use in a select
statement..
If there is a MAX of 2 sites on a report, you might create 2 parameters and
allow a single select on each... This feels a little kludgy, but maybe your
users would accept it..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ryan Johnson" <RyanJohnson@.discussions.microsoft.com> wrote in message
news:CE707855-1795-42CC-83BA-CB308DD0820C@.microsoft.com...
> Hi There
> My client has asked that there existing SQL reports be used for Comparison
Reports.
> So for example at the moment - they Select a Company site and a department
to create a report.
> Now they want to have have a another site on the report - like a multipage
report in MS Access reports.
> Any ideas folks?