Need to combine string data from multiple columns into one column

When quering a table with given criteria, For ex:

select notes, jobid, caller from contact where status in (6) and jobid = 173

I am getting this:

This job will be posted to Monster for 2 weeks. 173 906
Waiting for full budget approval 173 906
TUrns out we're uppin 173 906

What should I do so that these three columns for the same jobid from the same caller appears in only one column, either separated by a comma or semicolon?

Please HELP!!!!!

Concatenating row values in Transact-SQL

http://www.projectdmx.com/tsql/rowconcatenate.aspx

AMB

|||

You can concantenate the results, but you need to ensure that you have converted all the different data types to varchar. E.g.

Code Snippet

SELECT notes + ' , ' + CAST(jobid as varchar(100)) + ' , ' + CAST(caller as varchar(100)) FROM contact WHERE status in (6) and jobid = 173

I assumed that jobid and caller are int fields.

HTH

Ray

|||

Here it is (if you use SQL Server 2005),

Code Snippet

select distinct

(

select

notes + ';' as [text()]

from

contact sub

where

sub.caller=main.caller

and sub.jobid=main.jobid

for xml path('')

) as notes,

jobid,

caller

from

contact main

where

status in (6)

and jobid = 173

|||

Thanks for the response sekaran. I should have mentioned it before, I am using sql server 2000 using tsql language. Also the notes columns is of text type which I will cast as nvarchar(3500). I am having problems running you code. What am i doing wrong? can u help?

Need to change connection string for SQL 2005?

Hi,
DevStudio 2003, SQL Server 2005, Windows 2000 Pro SP4
I have been using the Enterprise Library Data Access Application Block to
connect to a local SQL 2000 database. The connection string parameters are:
<connectionStrings>
<connectionString name="Temp Connection String">
<parameters>
<parameter name="database" value="Temp" isSensitive="false" />
<parameter name="Integrated Security" value="True" isSensitive="false"
/>
<parameter name="packet size" value="4096" isSensitive="false" />
<parameter name="persist security info" value="false"
isSensitive="false" />
<parameter name="server" value="(local)" isSensitive="false" />
</parameters>
</connectionString>
</connectionStrings>
Having then removed SQL2000 and replaced it with SQL2005, the above
connection parameters no longer work. In order to connect, I'm having to
replace "(local)" with the explicit IP address of the local PC.
Can SQL 2005 be made to work with "(local)"? Having to change the "server"
value to the IP address means changing my installer to fill in this value,
which I'd rather not do. It also means that other developers in the team
have to change the connection string parameters in order to test on their
local databases.
For your information, SQL 2005 is configured to use both the Shared Memory &
Named Pipes protocols. Native Client configuration WAS setup with Shared
Memory first and Named Pipes second. I've tried disabling Shared Memory and
forcing Named Pipes to be used (on the basis that the SQL 2000 installation
was using Named Pipes) but to no avail.
Regards
David RazzettiHmmh, (local) should work on SQL2k5, whats the error message the
application Block is throwing ?
HTH, jens Suessmeyer.|||Hi Jens,
I'm using the application block to serve my code with SqlConnection objects.
The application block successfully supplies a SqlConnection object, but a
call to 'Open()' on the connection throws the SqlException "SQL Server does
not exist or access denied".
Regards
David Razzetti
"Jens" wrote:

> Hmmh, (local) should work on SQL2k5, whats the error message the
> application Block is throwing ?
> HTH, jens Suessmeyer.
>|||Hmmh, strange one. Didi you try just a "." for connecting to SQL Server
?
HTH, Jens Suessmeyer.|||Hi Jens,
"." appears to work correctly with both SQL 2000 and SQL 2005, so I guess I
can use that instead of "(local)". I'd still be interested to hear from
anyone who knows why "(local)" might not be working on my installation of SQ
L
2005.
Regards
David Razzetti
"Jens" wrote:

> Hmmh, strange one. Didi you try just a "." for connecting to SQL Server
> ?
> HTH, Jens Suessmeyer.
>|||Don=B4t know what version / build you are using but in earlier build the
local was replaced by localhost.
HTH, jens Suessmeyer.

Need to change connection string for SQL 2005?

Hi,
DevStudio 2003, SQL Server 2005, Windows 2000 Pro SP4
I have been using the Enterprise Library Data Access Application Block to
connect to a local SQL 2000 database. The connection string parameters are:
<connectionStrings>
<connectionString name="Temp Connection String">
<parameters>
<parameter name="database" value="Temp" isSensitive="false" />
<parameter name="Integrated Security" value="True" isSensitive="false"
/>
<parameter name="packet size" value="4096" isSensitive="false" />
<parameter name="persist security info" value="false"
isSensitive="false" />
<parameter name="server" value="(local)" isSensitive="false" />
</parameters>
</connectionString>
</connectionStrings>
Having then removed SQL2000 and replaced it with SQL2005, the above
connection parameters no longer work. In order to connect, I'm having to
replace "(local)" with the explicit IP address of the local PC.
Can SQL 2005 be made to work with "(local)"? Having to change the "server"
value to the IP address means changing my installer to fill in this value,
which I'd rather not do. It also means that other developers in the team
have to change the connection string parameters in order to test on their
local databases.
For your information, SQL 2005 is configured to use both the Shared Memory &
Named Pipes protocols. Native Client configuration WAS setup with Shared
Memory first and Named Pipes second. I've tried disabling Shared Memory and
forcing Named Pipes to be used (on the basis that the SQL 2000 installation
was using Named Pipes) but to no avail.
Regards
David Razzetti
Hmmh, (local) should work on SQL2k5, whats the error message the
application Block is throwing ?
HTH, jens Suessmeyer.
|||Hi Jens,
I'm using the application block to serve my code with SqlConnection objects.
The application block successfully supplies a SqlConnection object, but a
call to 'Open()' on the connection throws the SqlException "SQL Server does
not exist or access denied".
Regards
David Razzetti
"Jens" wrote:

> Hmmh, (local) should work on SQL2k5, whats the error message the
> application Block is throwing ?
> HTH, jens Suessmeyer.
>
|||Hmmh, strange one. Didi you try just a "." for connecting to SQL Server
?
HTH, Jens Suessmeyer.
|||Hi Jens,
"." appears to work correctly with both SQL 2000 and SQL 2005, so I guess I
can use that instead of "(local)". I'd still be interested to hear from
anyone who knows why "(local)" might not be working on my installation of SQL
2005.
Regards
David Razzetti
"Jens" wrote:

> Hmmh, strange one. Didi you try just a "." for connecting to SQL Server
> ?
> HTH, Jens Suessmeyer.
>
|||Don=B4t know what version / build you are using but in earlier build the
local was replaced by localhost.
HTH, jens Suessmeyer.
sql

Need suggestion or comments for n-Tier applications.

using System;namespace BaResearch.Data.msSql{interface ISqlDataObject { System.Data.SqlClient.SqlConnection Connection {get; }string ConnectionString {get;set; } }}

___________________________________

using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlClient;namespace BaResearch.Data.msSql{public class SqlDataObject : ISqlDataObject {private string _ConnectionString;public SqlConnection Connection {get {return new SqlConnection(_ConnectionString); } }public string ConnectionString {get {return _ConnectionString; }set { _ConnectionString =value; } } }}

__________________________________

using System;namespace BaResearch.Data{interface IBid {string Client {get;set; }string Contact {get;set; }string Sponsor {get;set; }string Priority {get;set; }string BidStatus {get;set; } }}

_____________________________________________

using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlClient;using System.Configuration;namespace BaResearch.Data.msSql{public class Bid : SqlDataObject, IBid {#region private member & variablesprivate int _BidID;private string _DateCreated;private string _CreatedBy;private string _Client;private string _Contact;private string _Sponsor;private string _Priority;private string _BidStatus;private SqlDataAdapter sqlda =new SqlDataAdapter();private SqlCommand _command;private SqlParameter[] _parameters = {new SqlParameter("@.client",SqlDbType.NVarChar),new SqlParameter("@.contact",SqlDbType.NVarChar),new SqlParameter("@.sponsor",SqlDbType.NVarChar),new SqlParameter("@.priority",SqlDbType.NVarChar),new SqlParameter("@.bidstatus",SqlDbType.NVarChar), };#endregion #region public member variables - stored procedures//private string sp_Create = "bids_sp_insert"; //private string sp_Update_ByID = "bids_sp_updatebyid"; //private string sp_Delete_ByID = "bids_sp_deletebyid"; //private string sp_Select_All = "bids_sp_selectall"; //private string sp_Select_ByID = "bids_sp_selectbyid"; //private string sp_Select_ByValue = "bids_sp_selectbyvalue";#endregion #region constructors and destructorspublic Bid() {if (this.ConnectionString ==null) {this.ConnectionString = ConfigurationManager.ConnectionStrings["SQL.ConnectionString"].ConnectionString; }else {new Bid(this.ConnectionString); } }public Bid(string ConnectionString) {this.ConnectionString = ConnectionString; }#endregion #region methods & events/// <summary> /// Attach parameters to an SqlCommand /// </summary> /// <param name="sqlcmd">SqlCommand</param>private void attachparameters(SqlCommand sqlcmd) { sqlcmd.Parameters.Clear();foreach (SqlParameter paramin _parameters) { sqlcmd.Parameters.Add(param); } }/// <summary> /// Assign value to the parameters /// </summary> /// <param name="sqlcmd">SqlCommand</param> /// <param name="value">Client object</param>private void assignparametervalues(SqlCommand sqlcmd, Bidvalue) {this.attachparameters(sqlcmd);// todo: assign parameter values here sqlcmd.Parameters[0].Value =value.Client; sqlcmd.Parameters[1].Value =value.Contact; sqlcmd.Parameters[2].Value =value.Sponsor; sqlcmd.Parameters[3].Value =value.Priority; sqlcmd.Parameters[4].Value =value.BidStatus; }public virtual void Create(Bidvalue) { _command =new SqlCommand(); _command.Connection =this.Connection; _command.CommandText = @."INSERT INTO bids (client, contact, sponsor, priority, bidstatus, createdby) VALUES (@.client, @.contact, @.sponsor, @.priority, @.bidstatus, @.createdby);"; _command.CommandType = CommandType.Text;try { sqlda.InsertCommand = _command;this.assignparametervalues(sqlda.InsertCommand,value); sqlda.InsertCommand.Parameters.AddWithValue("@.createdby",value.CreatedBy); sqlda.InsertCommand.Parameters.Add("@.bidid", SqlDbType.Int); sqlda.InsertCommand.Parameters["@.bidid"].Direction = ParameterDirection.ReturnValue; sqlda.InsertCommand.Connection.Open(); sqlda.InsertCommand.ExecuteNonQuery(); sqlda.InsertCommand.Connection.Close(); sqlda.InsertCommand.Connection.Dispose(); }catch (Exception e) {throw new Exception(e.Message); } }public virtual int Update(Bidvalue) {int result; _command =new SqlCommand(); _command.Connection =this.Connection; _command.CommandText = @."UPDATE bids SET client=@.client, contact=@.contact, sponsor=@.sponsor, priority=@.priority, bidstatus=@.bidstatus WHERE bidid=@.bidid"; _command.CommandType = CommandType.Text;try { sqlda.UpdateCommand = _command;using (SqlCommand _cmd = sqlda.UpdateCommand) {this.assignparametervalues(_cmd,value); _cmd.Parameters.AddWithValue("@.bidid",value.BidID); _cmd.Connection.Open(); result = _cmd.ExecuteNonQuery(); _cmd.Connection.Close(); _cmd.Connection.Dispose(); } }catch (Exception e) {throw new Exception(e.Message); }return result; }public virtual void Delete(Bidvalue) { _command =new SqlCommand(); _command.Connection =this.Connection; _command.CommandText = @."DELETE bids WHERE bidid=@.bidid;"; _command.CommandType = CommandType.Text;try { sqlda.DeleteCommand = _command;using (SqlCommand _cmd = sqlda.DeleteCommand) { _cmd.Parameters.AddWithValue("@.bidid",value.BidID); _cmd.Connection.Open(); _cmd.ExecuteNonQuery(); _cmd.Connection.Close(); _cmd.Connection.Dispose(); } }catch (Exception e) {throw new Exception(e.Message); } }public virtual DataSet List(string filter) { DataSet result =new DataSet(); _command =new SqlCommand(); _command.Connection =this.Connection; _command.CommandText = @."SELECT * FROM bids_view_listall WHERE (clientname like @.value) or (contactname like @.value) or (sponsor like @.value) ORDER BY datecreated DESC"; _command.CommandType = CommandType.Text;try {if (filter !=null) { filter = filter.Replace(" ","%"); } sqlda.SelectCommand = _command; sqlda.SelectCommand.Parameters.AddWithValue("@.value","%" + filter +"%"); sqlda.Fill(result); }catch (Exception e) {throw new Exception(e.Message); }return (DataSet)result; }#endregion #region IBid Memberspublic string Client {get {return _Client; }set { _Client =value; } }public string Contact {get {return _Contact; }set { _Contact =value; } }public string Sponsor {get {return _Sponsor; }set { _Sponsor =value; } }public string Priority {get {return _Priority; }set { _Priority =value; } }public string BidStatus {get {return _BidStatus; }set { _BidStatus =value; } }#endregion #region Propertiespublic int BidID {get {return _BidID; }set { _BidID =value; } }public string DateCreated {get {return _DateCreated; }set { _DateCreated =value; } }public string CreatedBy {get {return _CreatedBy; }set { _CreatedBy =value; } }#endregion }}

_____________________________________________

Am I on a right path? I will greatly appreciate in any comments or suggestions for a design. I am creating an n-tier application and i don't know if my design is right. I dont have the proper schooling for creating this kind of applications and I am still on layer of data access and business logic.

I tested it on a console application and web app, for now its working. But, how about if a change a different type of database? I studied some sample applications downloaded online and a little bit of reading from a book.

Although, I already completed a past project with a module (dll) for a web application and already have implemented it.
I am still confused on how to implement a data library that can be used in different type of applications and services (windows, web) and can change the type of database by changing some configuration and not change the code itself.

I there someone who can show me a pattern for creating a library that can be used to any type of applications. It will be a great help for me and I will greatly appreciate it.

|||

since no one replied, i have to search it for my self

http://www.dofactory.com/Patterns/Patterns.aspx.

I still didn't buy it, but, are these kind of books going to help me on what am i searching for?

|||

Sorry did not reply you sooner but here are some good samples, and the database agnostic stuff not that simple but check the Achitecture forum for references to Petshop a Java application converted to .NET by Microsoft. And good books I prefer Martin Fowler books but he is not a beginner's writer the best beginner's book is by Craig Larman because he is complete.

http://www.microsoft.com/belux/msdn/nl/community/columns/hyatt/ntier1.mspx

http://www.microsoft.com/belux/msdn/nl/community/columns/hyatt/ntier2.mspx

http://www.microsoft.com/belux/msdn/nl/community/columns/hyatt/ntier3.mspx

http://www.dotnetbips.com/articles/displayarticle.aspx?id=515

Free Objects development resources, Post again if you still have question. Hope this helps.

http://forums.asp.net/thread/1462028.aspx

|||

Thanks for your help.

|||

jd2001:

Thanks for your help.

I am glad I could help

Need SQL query

Hi there. U have table structure shown here:
category (Int - AutoIncrement) parent_category(Int) Title(string)
1 0
HOME PAGE
2 1
FIRST DEPTH 1
3 1
FIRST DEPTH 2
4 1
FIRST DEPTH 3
5 2
SECOND DEPTH 1
6 3
SECOND DEPTH 2
7 3
SECOND DEPTH 3
8 6
THIRD DEPTH 1
I want 1 (one) SQL query that can return me full depth for certain caregory.
In some meta-language
for category 8 it look like this:
SELECT * FROM table WHERE category = 8
WHILE parent_category <> 1
SELECT * FROM table WHERE category = [parent_category from previous
iteration]
WEND
So in that case resulting recordset would be
category (Int - AutoIncrement) parent_category(Int) Title(string)
8 6
HOME PAGE
6 3
FIRST DEPTH 1
3 1
FIRST DEPTH 2
I know that I can do this using more that one recordset, but I want all this
in one recordset.
I am using ADO, not ADO.NET
ThanksBola,
You can achieve this using user defined function, see following example.
create table tree
(category Int, parent_category Int, Title varchar(50))
go
insert into tree
select 1, 0,'HOME PAGE' union all
select 2, 1,'FIRST DEPTH 1' union all
select 3, 1,'FIRST DEPTH 2' union all
select 4, 1,'FIRST DEPTH 3' union all
select 5, 2,'SECOND DEPTH 1' union all
select 6, 3,'SECOND DEPTH 2' union all
select 7, 3,'SECOND DEPTH 3' union all
select 8, 6,'THIRD DEPTH 1'
select * from tree
go
create function fn_get_tree (@.y int)
returns
@.tb table(empid int,
supervisor int,
empname varchar(300)
)
as
begin
declare @.x table (empid int)
insert into @.x
select parent_category from tree where category = @.y
union all
select @.y
while 1=1
begin
insert into @.x
select parent_category from tree where category in (select distinct empid from @.x)
and parent_category not in(Select empid from @.x)
if @.@.rowcount = 0
break
end
insert into @.tb
select * from tree where
exists
(select * from @.x a where a.empid= tree.category)
return
end
go
--usage
select * from fn_get_tree (7)
--
- Vishal

Need some help in searching

Dear ASP.NET

How can I find records that contain a STRING from some (more than one) other fields ?

for example, I have:

Name_First = "aaa"
Name_Middle = "bbb"
Name_Last = "ccc"

Key_Words = "aaa,bbb,ccc"
(includes all values - comma separated)

How can I do the SELECT so that when I search for "bbb" on Key_Words I will get my record ?

Should I use "LIKE %aaa%" or something like this ?
(should I keep the comma separators ?)

Thanks in advance, Yovav.You can use like % for sure. Generally when I have searching functions where a user can enter one or more fields

I apppend each field in the SQL string as I build it. So for example mine may look something like the following:

DECLARE @.SQLWHERE varchar(1000)
SET @.SQLWHERE = ''

IF @.FirstName IS NOT NULL
SELECT @.SQLWHERE = ' WHERE FirstName ' + ' LIKE ''' + '%' + @.FirstName + '%'''

IF @.LastName IS NOT NULL
BEGIN
IF @.SQLWHERE = ''
SELECT @.SQLWHERE = @.SQLWHERE + ' WHERE P.LastName ' + ' LIKE ''' + '%' +
@.LastName + '%'''
ELSE
SELECT @.SQLWHERE = @.SQLWHERE + ' AND P.LastName ' + ' LIKE ''' + '%' + @.LastName + '%'''
END

And you can keep doing that type of logic for as many as you would like,

Another option is not to include any "%" at all and then indicate on your textboxes where users search that they can enter a * or % for like searches.

Just food for thought.|||I doing this search on 6 NTEXT fields...
so I thought maybe it will be faster if I do it on one field that has the content of the all 6...

what do U think ?

+
How should I write it ?
"SELECT * FROM X WHERE Key_Words LIKE %'" & SearchString & "'%" ?|||' SELECT * FROM X WHERE FirstName ' + ' LIKE ''' + '%' + @.FirstName + '%'''
.LastName ' + ' LIKE ''' + '%' + @.LastName + '%'''

ANd keep appending for each additional field until you have all six included.

If this is a stored procedure assign your string to variable and then at the end
call exec yourVarialbename

For example

EXEC (@.SQLStatement)

Need Query Help for Search

I am writing a small search feature to return a list of companies whose name "Begins with" a certain string (up to 5 chars) provided by the user via a textbox. I want the results to only return results that begin with the letter/letters specified. Below I will put the code that I came up with that isn't working quite how I expected. I am new to this so any assistance and short explanation would be very much apperciated.

sql="SELECT distinct cm.cmmst_id, cm.cm_compno, cm.cm_cname1 + ' ' + cm.cm_cname2 AS cm_cname1, cm_tele, cm_fax, cm_s16 "

sql=sql & "FROM cmmst cm "

sql=sql & "WHERE cm_cname1 + ' ' + cm_cname2 LIKE '%" companyNameBegins,"'","''") & "%' " sql=sql & "AND (cm_mbtyp='M' OR cm_mbtyp='SUBDIV') " sql=sql & "ORDER BY cm_s16 DESC, cm_cname1 ASC"

companyNameBegins is the string passed in by the user

Thanks,

Zoop

(1) Use parameterized Queries. Your code will look simpler and neater and you can avoid SQL Injection Attacks (Google for more info on this topic)

(2) Append the "%" in the value rather than in the SQL. Also if you want to retrieve records starting with a value the % should be at the end like "SELECT...WHERE column like 'startwith%'"

Here's some sample code:

Dim myCommand As SqlCommand
Dim myParam As SqlParameter

myCommand = New SqlCommand()
myCommand.Connection = objcon
myCommand.CommandText = "SELECT distinct cm.cmmst_id, cm.cm_compno, cm.cm_cname1 + ' ' + cm.cm_cname2 AS cm_cname1, cm_tele, cm_fax, cm_s16 FROM cmmst cm WHERE cm_cname1 + ' ' + cm_cname2 LIKE @.companyNameBegins AND (cm_mbtyp='M' OR cm_mbtyp='SUBDIV') ORDER BY cm_s16 DESC, cm_cname1 ASC"

myCommand.Parameters.Add(New SqlParameter("@.companyNameBegins ",SqlDbType.varchar,100))
myCommand.Parameters("@.companyNameBegins").Value = companyNameBegins & "%"

Try
If objCon.State = 0 Then objCon.Open()
'ExecuteReader and fill some dataContainer.

Catch exc As Exception
Response.Write(exc)
Finally
If objCon.State = ConnectionState.Open Then
objCon.Close()
End If
End Try

|||

Thanks for the assistance, much smoother this way.

zoop

need info from 2 tables

Here's my string. I know it's way wrong right now

SELECT binbox_receipt.partner_code
,binbox_receipt.link_id
,binbox_receipt_archive.partner_code
,binbox_receipt_archive.link_id
FROM binbox_receipt, binbox_receipt_archive
where
binbox_receipt_archive.link_id = binbox_receipt.link_id
and binbox_receipt.partner_code = '1154' and binbox_receipt.link_id = '2684'

and (binbox_receipt_archive.partner_code = '1154' and binbox_receipt_archive.link_id = '2684')

I need to check 2 tables if in the first table the link_id and partner_code exist or the second table link_id and partner_code exist

any help would be greatly appreciated I'm a little new at this but having fun trying

Does this work?

SELECT binbox_receipt.partner_code ,binbox_receipt.link_id ,binbox_receipt_archive.partner_code,binbox_receipt_archive.link_idFROM binbox_receiptINNERJOIN binbox_receipt_archiveON binbox_receipt_archive.link_id = binbox_receipt.link_idWHERE binbox_receipt.partner_code ='1154'and binbox_receipt.link_id ='2684'OR (binbox_receipt_archive.partner_code ='1154'and binbox_receipt_archive.link_id ='2684')

|||

no errors but no result either. I ran the query for a minute.

individually running the tables for the individual searches it querys quick though

|||

SELECT order_id ,binbox_receipt.partner_code
,binbox_receipt.link_id

FROM binbox_receipt
where binbox_receipt.partner_code = '1154' and binbox_receipt.link_id = '2684'

this statement returns 34 records

I need to also check the binbox_receipt_archive for records

|||

Try this:

IFEXISTS (SELECT 1FROM binbox_receipt_archive)begin-- here are some records in archive /* do something, for example */SELECT order_id ,binbox_archive.partner_code ,binbox_archive.link_idFROM binbox_archivewhere binbox_archive.partner_code ='1154'and binbox_archive.link_id ='2684'end-- the are some records in archiveelse-- there are no records in archivebegin-- there are no records in atchive /* do something, for example */SELECT order_id ,binbox_receipt.partner_code ,binbox_receipt.link_idFROM binbox_receiptwhere binbox_receipt.partner_code ='1154'and binbox_receipt.link_id ='2684'end-- there are no records in archive

Good luck.

|||

I think this is it But not 100% sure

IF EXISTS (SELECT 1 FROM binbox_receipt_archive)or EXISTS (SELECT 1 FROM binbox_receipt)
begin -- here are some records in archive

/* do something, for example */

SELECT order_id ,binbox_receipt.partner_code
,binbox_receipt.link_id

FROM binbox_receipt
where binbox_receipt.partner_code = '1154' and binbox_receipt.link_id = '2684'

end -- the are some records in archive
begin -- there are no records in archive

/* do something, for example */
SELECT order_id ,binbox_receipt_archive.partner_code
,binbox_receipt_archive.link_id

FROM binbox_receipt_archive
where binbox_receipt_archive.partner_code = '1154' and binbox_receipt_archive.link_id = '2684'

end -- there are no records in archive|||

Explain in detail your requirement. Do you care if the values are present in either databace - value1 in table1 and value2 in table2? or do both values have to be in one table in order to select records from it.

|||

it must first search for partner_code and link_id in table 1 and then search for partner_code and link_id in table 2. if its in table 1 display result and or if its in table 2 display results

|||

if something exists in either table I want to see the results

|||

Try the UNION or UNION ALL.

If this doesnt work, please post some sample data from each table and expected output.

SELECT binbox_receipt.partner_code ,binbox_receipt.link_idFROM binbox_receiptWHERE ( binbox_receipt.partner_code ='1154'And binbox_receipt.link_id ='2684')UNION SELECT ,binbox_receipt_archive.partner_code,binbox_receipt_archive.link_idFROM binbox_receipt_archiveWHERE ( binbox_receipt_archive.partner_code ='1154'And binbox_receipt_archive.link_id ='2684')

|||

would this work?

begin
SELECT order_id ,binbox_receipt_archive.partner_code
,binbox_receipt_archive.link_id

FROM binbox_receipt_archive
where binbox_receipt_archive.partner_code = '1154' and binbox_receipt_archive.link_id = '2685'
end
begin
SELECT order_id ,binbox_receipt.partner_code
,binbox_receipt.link_id

FROM binbox_receipt
where binbox_receipt.partner_code = '1154' and binbox_receipt.link_id = '2685'
end|||

this works I needed to see if a record existed and this returns one record. I'd say this works for checking for a record the other one works for returning all records.

Thanks.

Need Help... Urgent!

I've installed SQL Server 2005 from MSDN, however, while I was trying to
install the SP1 from the same DVD, it said:
"Shared String ID 11217 Not Found" with the body of the message saying
"Shared String ID 11216 Not Found"...
What could I do? How do I solve this?
I went to Microsoft.com to download the SP1 directly from there.. But the
error continues...
Thanks...
Hi Sonia,
Why dont you apply SP2 directly?
Ekrem ?nsoy
"Sonia.Madureira" <Sonia.Madureira@.discussions.microsoft.com> wrote in
message news:067A08DC-D5B1-4734-B24D-3A23E951F562@.microsoft.com...
> I've installed SQL Server 2005 from MSDN, however, while I was trying to
> install the SP1 from the same DVD, it said:
> "Shared String ID 11217 Not Found" with the body of the message saying
> "Shared String ID 11216 Not Found"...
> What could I do? How do I solve this?
> I went to Microsoft.com to download the SP1 directly from there.. But the
> error continues...
> Thanks...
|||Hi Ekrem,
Thanks for answer.. I've tried right now to install SP2 but it didn't work
out either..
When it's extracting, occours an error saying:
"The following error occoured", same in body - OK
When I click in OK it shows:
"A recently applied update, , failed to install" and in the content of the
report it says:
"EventType: sqlsetup P1 : unknown P2 : 0x0 P3 : unknown ..." , the
unknown repeats until P10...
Can you Help me?
"Ekrem ?nsoy" wrote:
Hi Sonia,
Why dont you apply SP2 directly?
Ekrem ?nsoy
|||I'm not sure if your prior SP installation attempt broke some SQL Server
binaries or not but it look's like it did. As you do not have a chance to
uninstall only the SP installation (that corrupted one), there is not
emerging an idea in my mind but to uninstall the problematic SQL Server
instance and install a new SQL Server instance from scratch and applying SP2
then.
I don't know your environment and if it's an important SQL Server instance
and can not be offline for a while or something don't blame me for my
comments =)
I hope others has a better idea on this problem.
P.S.
You said that an error occurs when the package is being extracted but as it
complains about the corrupted SP installation in the error message, I think
it finishes extracting the package and the problem occurs when it's checking
the system before applying the SP.
Ekrem ?nsoy
"Sonia.Madureira" <SoniaMadureira@.discussions.microsoft.com> wrote in
message news:523C6A93-BF60-492A-A58F-DF9729087D2E@.microsoft.com...
> Hi Ekrem,
> Thanks for answer.. I've tried right now to install SP2 but it didn't work
> out either..
> When it's extracting, occours an error saying:
> "The following error occoured", same in body - OK
> When I click in OK it shows:
> "A recently applied update, , failed to install" and in the content of the
> report it says:
> "EventType: sqlsetup P1 : unknown P2 : 0x0 P3 : unknown ..." , the
> unknown repeats until P10...
> Can you Help me?
>
>
>
> "Ekrem ?nsoy" wrote:
> Hi Sonia,
> Why dont you apply SP2 directly?
> --
> Ekrem ?nsoy
>
|||Thanks Ekrem,
I'll uninstall SQL and try again.
I've started this week serving as aprenctice in a company of services,
software, harware... So, they want me to learn as logic that is... (by the
way, forgive me my mistakes, i'm portuguese)
We install PC's, servers, etc in clients.. So we use SQL server...
They told me to install SQL server in my PC to start learning and when
errors occour, they answer to looke online.. I am looking everywhere but with
no luck... =( LOL
Thanks for helping me Ekrom... I will try to uninstall now... And try
again... =)
"Ekrem ?nsoy" wrote:

> I'm not sure if your prior SP installation attempt broke some SQL Server
> binaries or not but it look's like it did. As you do not have a chance to
> uninstall only the SP installation (that corrupted one), there is not
> emerging an idea in my mind but to uninstall the problematic SQL Server
> instance and install a new SQL Server instance from scratch and applying SP2
> then.
> I don't know your environment and if it's an important SQL Server instance
> and can not be offline for a while or something don't blame me for my
> comments =)
> I hope others has a better idea on this problem.
> P.S.
> You said that an error occurs when the package is being extracted but as it
> complains about the corrupted SP installation in the error message, I think
> it finishes extracting the package and the problem occurs when it's checking
> the system before applying the SP.
> --
> Ekrem ?nsoy
|||Thanks Ekrem,
I'm allready installing SP2...
I have my problem solved...
=)
|||That's a good news =)
I suggest you to be careful about installing SQL Server and its Service
Packs... For example, close any running apps that can\may prevent SQL Server
Setup to install its components successfully. It was your computer this time
but it could be an important SQL Server server. So ensure that there is a
clean and pure running system before starting installing SQL Server and its
Service Pack or something.
To learn better you could buy some books about SQL Server and your best
online assistant will be Books Online. Take advantage of it.
Good luck.
Ekrem ?nsoy
"Sonia.Madureira" <SoniaMadureira@.discussions.microsoft.com> wrote in
message news:BDDAFBE5-0CE4-4831-8827-314F87C4E5F0@.microsoft.com...
> Thanks Ekrem,
> I'm allready installing SP2...
> I have my problem solved...
> =)

Need Help... Urgent!

I've installed SQL Server 2005 from MSDN, however, while I was trying to
install the SP1 from the same DVD, it said:
"Shared String ID 11217 Not Found" with the body of the message saying
"Shared String ID 11216 Not Found"...
What could I do? How do I solve this?
I went to Microsoft.com to download the SP1 directly from there.. But the
error continues...
Thanks...Hi Sonia,
Why dont you apply SP2 directly?
--
Ekrem Ã?nsoy
"Sonia.Madureira" <Sonia.Madureira@.discussions.microsoft.com> wrote in
message news:067A08DC-D5B1-4734-B24D-3A23E951F562@.microsoft.com...
> I've installed SQL Server 2005 from MSDN, however, while I was trying to
> install the SP1 from the same DVD, it said:
> "Shared String ID 11217 Not Found" with the body of the message saying
> "Shared String ID 11216 Not Found"...
> What could I do? How do I solve this?
> I went to Microsoft.com to download the SP1 directly from there.. But the
> error continues...
> Thanks...|||Hi Ekrem,
Thanks for answer.. I've tried right now to install SP2 but it didn't work
out either..
When it's extracting, occours an error saying:
"The following error occoured", same in body - OK
When I click in OK it shows:
"A recently applied update, , failed to install" and in the content of the
report it says:
"EventType: sqlsetup P1 : unknown P2 : 0x0 P3 : unknown ..." , the
unknown repeats until P10...
Can you Help me?
"Ekrem Ã?nsoy" wrote:
Hi Sonia,
Why dont you apply SP2 directly?
--
Ekrem Ã?nsoy|||I'm not sure if your prior SP installation attempt broke some SQL Server
binaries or not but it look's like it did. As you do not have a chance to
uninstall only the SP installation (that corrupted one), there is not
emerging an idea in my mind but to uninstall the problematic SQL Server
instance and install a new SQL Server instance from scratch and applying SP2
then.
I don't know your environment and if it's an important SQL Server instance
and can not be offline for a while or something don't blame me for my
comments =)
I hope others has a better idea on this problem.
P.S.
You said that an error occurs when the package is being extracted but as it
complains about the corrupted SP installation in the error message, I think
it finishes extracting the package and the problem occurs when it's checking
the system before applying the SP.
--
Ekrem Ã?nsoy
"Sonia.Madureira" <SoniaMadureira@.discussions.microsoft.com> wrote in
message news:523C6A93-BF60-492A-A58F-DF9729087D2E@.microsoft.com...
> Hi Ekrem,
> Thanks for answer.. I've tried right now to install SP2 but it didn't work
> out either..
> When it's extracting, occours an error saying:
> "The following error occoured", same in body - OK
> When I click in OK it shows:
> "A recently applied update, , failed to install" and in the content of the
> report it says:
> "EventType: sqlsetup P1 : unknown P2 : 0x0 P3 : unknown ..." , the
> unknown repeats until P10...
> Can you Help me?
>
>
>
> "Ekrem Ã?nsoy" wrote:
> Hi Sonia,
> Why dont you apply SP2 directly?
> --
> Ekrem Ã?nsoy
>|||Thanks Ekrem,
I'll uninstall SQL and try again.
I've started this week serving as aprenctice in a company of services,
software, harware... So, they want me to learn as logic that is... (by the
way, forgive me my mistakes, i'm portuguese)
We install PC's, servers, etc in clients.. So we use SQL server...
They told me to install SQL server in my PC to start learning and when
errors occour, they answer to looke online.. I am looking everywhere but with
no luck... =( LOL
Thanks for helping me Ekrom... I will try to uninstall now... And try
again... =)
"Ekrem Ã?nsoy" wrote:
> I'm not sure if your prior SP installation attempt broke some SQL Server
> binaries or not but it look's like it did. As you do not have a chance to
> uninstall only the SP installation (that corrupted one), there is not
> emerging an idea in my mind but to uninstall the problematic SQL Server
> instance and install a new SQL Server instance from scratch and applying SP2
> then.
> I don't know your environment and if it's an important SQL Server instance
> and can not be offline for a while or something don't blame me for my
> comments =)
> I hope others has a better idea on this problem.
> P.S.
> You said that an error occurs when the package is being extracted but as it
> complains about the corrupted SP installation in the error message, I think
> it finishes extracting the package and the problem occurs when it's checking
> the system before applying the SP.
> --
> Ekrem Ã?nsoy|||Thanks Ekrem,
I'm allready installing SP2...
I have my problem solved...
=)|||That's a good news =)
I suggest you to be careful about installing SQL Server and its Service
Packs... For example, close any running apps that can\may prevent SQL Server
Setup to install its components successfully. It was your computer this time
but it could be an important SQL Server server. So ensure that there is a
clean and pure running system before starting installing SQL Server and its
Service Pack or something.
To learn better you could buy some books about SQL Server and your best
online assistant will be Books Online. Take advantage of it.
Good luck.
--
Ekrem Ã?nsoy
"Sonia.Madureira" <SoniaMadureira@.discussions.microsoft.com> wrote in
message news:BDDAFBE5-0CE4-4831-8827-314F87C4E5F0@.microsoft.com...
> Thanks Ekrem,
> I'm allready installing SP2...
> I have my problem solved...
> =)|||Thanks so much for helping me...
I have a lot to learn... =)
i will look for ebooks about it...
Thanks again!

need help, How to change datatype in order to sort some extracted numbers from string

Hello,

I am trying to extract from some strings like the following strings the number and order them by that number:

Box 1
Box 2
Box 3
Box 20
Box 21
...(and so on)

The problem I am having is that I already extracted the number using

Substring([field],[starting position],[lenght])

but the output seems to be in a string format, so the order is not in an ascending order.

Thanks for any suggestions.

You need to cast the result as a number. It looks like your number is always an integer so the following should do it:

(DT_I4)Substring([field],[starting position],[lenght])

-Jamie

|||I tried it but I am getting an SQL Execution Error message,

I am doing this on the Query Builder using Visual Studio 2005.net

|||

If you're getting an error message its useful to post it up here!

-Jamie

|||Sorry, I am behind a very restrictive firewall at work, doesnt let me get inthere!

Why should I post it somewhere else, anyways?

|||

mendez_edd wrote:

Sorry, I am behind a very restrictive firewall at work, doesnt let me get inthere!

It doesn't let you get to where? Can you replicate the error in BIDS?

mendez_edd wrote:

Why should I post it somewhere else, anyways?

Posting the error message helps people deduce what is causing the error!

-Jamie

|||

Where are you doing this? Jamie’s solution was for a SSIS expression as you might use in the Derived Column Transform. Your did mention a SQL exception, but since this is a SSIS forum, we are kind of thinking you may want a SSIS solution.

To sort like a number, convert the value to a number, so strip off the text as Jamie suggested, or pad the numeric part with spaces ans sort as a string–
Box 1
Box 2
Box 21
Box 22

(Note the extra space for single digit numbers.)

Regardless of your firewall you can post to this forum, so could you not type the error message text you get into a post?