help! trying to find records inside text string

Hi, got a problem and can't figure this one out.

basically i've got a field containing a value '(14)(12)(33)(22)' and i want to compare it to a table containing those values in separate cells...

record 1 : (01)
record 2 : (02)
etc...

and i want to compare this one field to those records to see whether that text string contains anything the table contains.

i've tried ContainsTable & IN but still can't figure this out.

any help greatly appreciated
:owould you kindly give us the table names and column names involved

otherwise the most i can tell you is to use LIKE

HELP! Stored Procedure Problem

I need to pull distinct records in my SP, but if there are different values in some of the columns it grabs those records too. I tried a nested query but I get an error that it is returning more than one value and not allowed.

I can grab the 1st record for each cust I need like this in a view:

SELECT TOP 100 PERCENT vcCustId, MIN(siEntry) AS MinRecNo, vcAdsource AS Ad
FROM dbo.PropReportData
GROUP BY vcCustId, vcAdsource
ORDER BY vcCustId

and then Inner Join it in my SP like this:

CREATE PROCEDURE Reports_GetReportData
(
@.cApartmentSite varchar(25),
@.vcAdSource varchar(50)
)
AS
SELECT *
FROM dbo.vPrePull INNER JOIN
 dbo.PropReportData ON dbo.vPrePull.MinRecNo = dbo.PropReportData.siEntry
WHERE
cApartmentSite = @.cApartmentSite and vcAdSource = @.vcAdSource
GO

Now, the problem is this, the parameters are in my stored procedure which is parsed second. So I am not getting the unique data to pull from.

Ultimately what I need to use is below with something inside it that will do what the view above did:

CREATE PROCEDURE Reports_GetReportData2
(
@.cApartmentSite varchar(25),
@.vcAdSource varchar(50)
)
AS
SELECT TOP 100 PERCENT vcCustId, MIN(siEntry) AS MinRecNo, vcAdsource AS Ad,sientry,vcProspectName,
vcPhone,vcEmail,vcDesiredHome,vcMoveInDate,vcStatus,vcVisitDate,vcComments
FROM dbo.PropReportData
WHERE
cApartmentSite = @.cApartmentSite and vcAdSource = @.vcAdSource
GROUP BY vcCustId, vcAdsource,sientry,vcProspectName,vcPhone,vcEmail,vcDesiredHome,vcMoveInDate,vcStatus,vcVisitDate,
vcComments
ORDER BY vcCustId
GO

Got to have this done by C.O.B. Monday or I may not have a job.
Thanks.What you want seems doable, but some more info (structure of the tables, some sample data) would help.

Not that I feel pressured or anything here...|||I'm using it to create reports for my CRM application and I used the Reports Starter Kit as my base. I am displaying Each Ad Source the Prospect called in on and the Prospects Record data in a tabular report. In the footer of each Ad Source it gives a count of the total leads that came in on that Ad Source. The problem is this, when a change is made it creates another record for that prospect with a unique record number; this has to be for history purposes, because I have another report that display all record activity, (that one works). So when I display the Prospect records it has a duplicate, which I can cull out by using vcCustID, MIN(siEntry) this gives me the first record entry for that Prospect, BUT, if one of the fields that I am trying to display was changed (like Visit Date in the example below), another instance of the Prospect is displayed. So for example I have a count of 2 unique leads by Ad Source and they call back and change their Visit Date, it will display a count of 2 leads (which is correct) and display 3 records (wrong), showing that Prospect twice.

Example:
ApartmentGuide.com
Prospect Name.Telephone.Email.DesiredHome.Move-In Date.Status.Visit Date
Bear, Smokey 911-911-9119 smokey@.nofire.com 1 x 1 10/31/2003 Visit Set10/23/2003
Bear, Smokey 911-911-9119 smokey@.nofire.com 1 x 1 10/31/2003 Visit Set10/26/2003
Walker, Johnny 555-645-7895 drunk@.booze.com 1 x 1 10/23/2003 Visit Set 10/23/2003

Total Leads this Ad Source: 2

I am pulling the data from a single table called PropReportData that has just the info I need for reporting. This was necessary because the information necessary to create a report is in 9 different tables and the amount of executes necessary to do the inner joins caused major performance issues and after running an execution plan it just didn't seem feasible to continue in that direction.

The table has vcCustID(Unique), cApartmentSite(used to associate Client to Cust to), siEntry(Unique Record Number), Ad Source, etc.

The last Stored Procedure in my first post is what I need to work, it has the parameters in it I need to display client specific data, which uses cApartmentSite. The value is picked up from the UserLogin and put in session to be used with my parameters and a few other things.

Each Prospect record created by my Marketing Associates for that client has this value inserted in a field in the PropReportData table creating the Client to Prospect relationship. So when the client logs it only pulls their information based on the cApartmentSite value in the tables.

Let me know if you need more info.

Thanks.|||This seems to work:

CREATE PROCEDURE Reports_PainInTheButt
(
@.cApartmentSite varchar(25),
@.vcAdSource varchar(50)
)
AS
SELECT DISTINCT
 TOP 100 PERCENT dbo.PropReportData.vcCustId, MIN(DISTINCT dbo.PropReportData.siEntry) AS siEntry,
 MIN(DISTINCT dbo.PropReportData.vcAdsource) AS vcAdsource,
 MIN(DISTINCT dbo.PropReportData.vcProspectName) AS vcProspectName, MIN(DISTINCT dbo.PropReportData.vcPhone) AS vcPhone,
 MIN(DISTINCT dbo.PropReportData.vcEmail) AS vcEmail, MIN(DISTINCT dbo.PropReportData.vcDesiredHome) AS vcDesiredHome,
 MIN(DISTINCT dbo.PropReportData.vcMoveInDate) AS vcMoveInDate, MIN(DISTINCT dbo.PropReportData.vcStatus) AS vcStatus,
 MIN(DISTINCT dbo.PropReportData.vcVisitDate) AS vcVisitDate, MIN(DISTINCT dbo.PropReportData.vcComments) AS vcComments
FROM dbo.PropReportData
WHERE dbo.PropReportData.cApartmentSite = @.cApartmentSite AND dbo.PropReportData.vcAdsource = @.vcAdSource
GROUP BY dbo.PropReportData.vcCustId
ORDER BY dbo.PropReportData.vcCustId
GO

If you see any potential problems with this let me know, it's all I can come up with.

Thanks.

HELP! More deleted records!

I have a replicated database and hundreds of records keep "disappearing" from
one table in particular. The table is a child table storing the answers to a
number of questions. Also;
1. The tables have referential integrity enforced, with cascade delete.
2. The relationships are enforced for replication.
3. There are no stored procedures which delete records from the offending
table.
4. There is no way of manually deleting records directly from the offending
table using the front-end application.
5. The parent tables have exactly the same records in as previously.
6. There are no replication conflicts.
Any ideas?
I am keeping transaction logs for 3 weeks. Is there any way that I can find
out from the transaction logs when the records were deleted? Is there any way
I can find out why the records would have been deleted?
Thanks,
Nigel Taylor
http://www.tinit.co.uk/
Nigel,
you don't say if this is on the publisher or on the subscriber, but I'm
guessing the latter. It could be that some updates are replicated as a
delete-insert pair for transactional replication in ceertain cases (see
http://support.microsoft.com/default...NoWebContent=1).
Perhaps this is your case?
Also I'm not clear why you're using cascading constraints for the subscriber
which is normally treated as RO - or maybe I have misunderstood?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
It's an ftp merge replication. I'm not sure where the records are
disappearing from first but the publisher is definately causing the other
subscribers (of which there are about 12) to lose the records too.
The relationships (i.e. cascade delete and enforce for replication) were
automatically created when the subscription was first created from the
publisher.
With the delete-insert pair the records would be recreated wouldn't they?
Thanks,
Nigel Taylor
http://www.tinit.co.uk/
|||In fact, when I use your spBrowseMergeChanges procedure on the offending
table, there are a load more records marked for deletion and without a
replacement!
Nigel Taylor
http://www.tinit.co.uk/
|||Nigel,
as you're using merge, and assuming you have all the related tables being
replicated, I'd modify all relationships to be not enforced for replication.
The delete-insert pairs are a problem for transactional and not merge, but
with merge there are other reasons the FK constraints are not applied (see
http://www.replicationanswers.com/Me...derArticle.asp). This
will simplify the setup somewhat and hopefully allow us to see if this is
the cause of the problems.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Okay, thanks Paul. I've removed the 'enforce relationship for replication'
flag and removed the cascade deletes on the offending tables. Hopefully it'll
work and I can get back to doing something else.
Cheers,
Nigel Taylor
http://www.tinit.co.uk/

Help! Indexing LARGE table

OK, I imported 680 million records into an unindexed table. That went well.

Then, I went into Enterprise Manager and added a two column non-unique clustered index to that table to speed access.

It's been running for ~36 hours and I have no idea when it will complete. I have deadlines that I'm going to miss and am very nervous; what can I do?

SQL Server 2000 Enterprise Edition (8.00.818 - sp3 + hotfixes)
Dual 3Ghz Xeon (two physical CPUs each have HyperThreading enabled)
Windows 2000 SP4
4GB RAM (although I just noticed the 3GB OS switch wasn't on)
SCSI boot drive
tempdb, data, and transaction log are on a FibreChannel RAID SAN

Help! Thanks in advance!OK, I imported 680 million records into an unindexed table. That went well.

Well what? Into the night?

Then, I went into Enterprise Manager and added a two column non-unique clustered index to that table to speed access.

Yeah, you'd want to do that with almost a billion records (hey let's not split hairs)

It's been running for ~36 hours and I have no idea when it will complete. I have deadlines that I'm going to miss and am very nervous; what can I do?

Pray?

SQL Server 2000 Enterprise Edition (8.00.818 - sp3 + hotfixes)
Dual 3Ghz Xeon (two physical CPUs each have HyperThreading enabled)
Windows 2000 SP4
4GB RAM (although I just noticed the 3GB OS switch wasn't on)
SCSI boot drive
tempdb, data, and transaction log are on a FibreChannel RAID SAN

Nice box...do you have to buy it dinner?

Help! Thanks in advance!

Help with what? You defenitley DONT want to kill it...it'll take twice as long to rollback.

Seems you get a lot of these. I would definetly start padding my estimates...

OR, I would think about performance before...

Without knowing you're data, I would still say that this is a MAJOR partition canidate, into many file groups on many different devices...

But that's just me

OH

MOO

Good Luck|||I agree with Brett that killing the job will be bad. A clustered index looks to order the rows of the table on the disk itself. If SQL Server decides that these rows are in exactly the revers order of what they should be, then you are going to see an awful lot of data move around. Your transaction log is probably going nuts, too.
As for speeding it up, there isn't a lot you can do while the process is in flight. If this import is a normal thing, then you can consider partitioning the table on some unique key, and cutting it down to seven 100 million row tables. Also, for non-clustered indexes, you can get a boost from the SORT_IN_TEMPDB option. Take a look in BOL for that.|||Without knowing you're data, I would still say that this is a MAJOR partition canidate, into many file groups on many different devices...

Yes, horizontal partitioning will be a necessary move. My data isn't primarily organized by date so that wouldn't make an ideal partition criteria. I haven't done this before and am nervous about doing it correctly; mistakes at the planning phase will be hard to correct.

Your reply is a mix of helpful advice and frustrated sarcasm. I am thankful for the helpful advice and probably deserve the sarcasm.|||ok, it just finished rolling back. The data file was on a 150GB SAN partition and ran out of space. It was previously like 73GB so I'm surprised that happened. any way, at least I have the database back...

thanks guys|||Your reply is a mix of helpful advice and frustrated sarcasm. I am thankful for the helpful advice and probably deserve the sarcasm.

Well I was trying to get you to laugh..not to make fun of you

Why not post the DDL of the table...|||Got me to laugh, anyway. Does that count as a near miss?

Roger: At this point, maybe you can try the index as a non-clustered index. Let us know what happens.|||Is there any chance the import file is already sorted? If so you can create the clustered index on the empty table and use the ORDERED argument with BCP to prevent SQL from resorting the data.|||Well I was trying to get you to laugh..not to make fun of you

Brett's gone off to Flordia for vacation. We can make fun of him while he is gone. :p

HELP! Cannot pass GUID's through variables?

I have a project that uses GUID's througout and I'm completely stumped.

1) I create a "batch" GUID to batch the records I'm about to process.

2) I call a web service on a remote machine, and reserve the batch records by inserting the batch GUID into a string works fine

3)I call another web service that returns the rows that I just reserved as XML objects and insert into a string variable

4)I need to use the "batch"GUID variable which is typed as a string (DT_WSTR) as an added column so in a Data Flow Task I do the following:

a) use the XML string variable as the source of a XML Source Task -- works (now that I'm passing custom objects and not a dataset -- curious as to why I can't consume a dataset but thats a different question)

This is where things get tricky:
I've tried to add the BatchGUID as a derived column, as a datatype of DT_WSTR (unicode string) and convert it to uniqueidentifier in the Data Conversion task error, cannot convert unicode to uniqueidentifier (I know I can in C# and SQL Server so why not here).

I've tried to CAST the BatchGUID as a uniqueidentifier and pass that to the datasource -- again conversion error.

I've tried using the type Object and Casting to anything and that doesn't work either.

I've tried to pass the unicode all the way to the SQL Server Destination -- and insert into UniqueIdentifier field... again no go.

All help would be appreciated, at this point I can't see any way of using a UniqueIdentifier as a key field, and maintaining it through the package...
Is this a bug?

Oh and if you want to have some real fun, try returning the type of UniqueIdentifier as an output parameter using a ADO.NET connection.

Thanks!

Maybe a parameterized insert using the SQL Task and a property expression on the query to exchange the value of the GUID ID variable into the insert statement? Have you tried that?

Kirk Haselden
Author "SQL Server Integration Services"

|||

Could you elaborate more on how to implement the property expression in the query? I am doing exqactly as you said using a SQL task and trying to insert into custom logging tables via a sp call. I am using parameter mapping to map system variables to parameters in my sql call, however in my sp I am defining the guids as uniqueidentifiers but ssis system variable guids are strings. What is the easiest way to handle this and convert so it works?

Thanks!

|||

There's some examples here:

Using dynamic SQL in an OLE DB Source component
http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

Setting Expressions
http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

Dynamic modification of SSIS packages
http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx

-Jamie

Help writing SQL

I want to select records where a column is null, is not null or is equal to a specific number. How do I do this in one sql statement. My application is an asp.net web site with a business logic layer, a data access layer and sql server. Thank you in advance.select *
from tb
where isnull(col,'*')=isnull(@.search,'*')

Help with Where clause

Hi,
I want to return records based on a linetype and classtype
Here is what I have so far
Where (LineType = '1' OR LineType = '7')
or (LineType = '5'AND ClassType NOT LIKE '[_]%')
I am trying to return all LineType 1 and 7 and only LineType 5 where the
classtype does not start with an underscore but the above syntax doesn't
return any records where the ClassType starts with an underscore regardless
of whether they are LineType 1, 7 or 5
How can I achieve my goal?
Thanks
On Thu, 17 Mar 2005 10:09:21 -0000, Newbie wrote:

>Hi,
>I want to return records based on a linetype and classtype
>Here is what I have so far
>Where (LineType = '1' OR LineType = '7')
>or (LineType = '5'AND ClassType NOT LIKE '[_]%')
>I am trying to return all LineType 1 and 7 and only LineType 5 where the
>classtype does not start with an underscore but the above syntax doesn't
>return any records where the ClassType starts with an underscore regardless
>of whether they are LineType 1, 7 or 5
>How can I achieve my goal?
>Thanks
>
Hi Newbie,
I'm sorry, but I could not reproduce the behaviour you're reporting. Run
the following script in Query Analyzer and check the results:
create table test(LineType char(1) not null, ClassType char(3) not null)
go
insert test select '1', '_as' union all select '1', 'dfg'
union all select '5', '_as' union all select '5', 'dfg'
union all select '7', '_as' union all select '7', 'dfg'
union all select '8', '_as' union all select '8', 'dfg'
go
select * from test
Where (LineType = '1' OR LineType = '7')
or (LineType = '5'AND ClassType NOT LIKE '[_]%')
go
drop table test
go
Results on my test database:
LineType ClassType
-- --
1 _as
1 dfg
5 dfg
7 _as
7 dfg
If you have different results, then please paste the output of
SELECT @.@.VERSION
in a reply to this message.
If you have the same results, then it must be something else. Please
post a simple script (as I did above) that allows me to reproduce the
behaviour. Include the expected output as well. (www.aspfaq.com/5006)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks _ I tried your script and it worked fine - the problem was my working
of the calculator - managed to make the same mistake more than once!
Thanks again
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:d5ui31d2np1k5khvonabvmbcdmpr1lollf@.4ax.com...
> On Thu, 17 Mar 2005 10:09:21 -0000, Newbie wrote:
>
> Hi Newbie,
> I'm sorry, but I could not reproduce the behaviour you're reporting. Run
> the following script in Query Analyzer and check the results:
> create table test(LineType char(1) not null, ClassType char(3) not null)
> go
> insert test select '1', '_as' union all select '1', 'dfg'
> union all select '5', '_as' union all select '5', 'dfg'
> union all select '7', '_as' union all select '7', 'dfg'
> union all select '8', '_as' union all select '8', 'dfg'
> go
> select * from test
> Where (LineType = '1' OR LineType = '7')
> or (LineType = '5'AND ClassType NOT LIKE '[_]%')
> go
> drop table test
> go
> Results on my test database:
> LineType ClassType
> -- --
> 1 _as
> 1 dfg
> 5 dfg
> 7 _as
> 7 dfg
> If you have different results, then please paste the output of
> SELECT @.@.VERSION
> in a reply to this message.
> If you have the same results, then it must be something else. Please
> post a simple script (as I did above) that allows me to reproduce the
> behaviour. Include the expected output as well. (www.aspfaq.com/5006)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Help with update query linking 3 tables

I am looking for some assistance with an update query that needs to link 3
tables:

This query ran and reported over 230,000 records affected but did not change
the field I wanted changed, not sure what it did.
I did notice that the "name" in "GM_NAMES.name" was colored blue in Query
Analyzer. Is it bad to name a column "name"?

UPDATE ABSENCES
set CustomerContactID = cicntp.cnt_id
from absences, cicntp
where (SELECT cicntp.cnt_l_name
FROM cicntp INNER JOIN
gm_names ON cicntp.cnt_l_name = GM_NAMES.name INNER JOIN
Absences ON cicntp.cnt_id = Absences.CustomerContactID)

Next I tried this query which is still running after 75 minutes (on a
laptop)

update absences
set CustomerContactID = cicntp.cnt_id
from absences, cicntp, gm_names
where gm_names.name= cicntp.cnt_l_name

As you can see, the 3 tables are ABSENCES, CICNTP and GM_NAMES.
Absences.CustomerContactID is what I need updated, when finished it should
match CICNTP.cnt_id
GM_NAMES is a temp table and matches records in CICNTP.cnt_l_name

Can some of you school this newbie on the best way to do this?
Thanks a bunch!On Fri, 15 Apr 2005 19:54:56 GMT, rdraider wrote:

>I am looking for some assistance with an update query that needs to link 3
>tables:
>This query ran and reported over 230,000 records affected but did not change
>the field I wanted changed, not sure what it did.
>I did notice that the "name" in "GM_NAMES.name" was colored blue in Query
>Analyzer. Is it bad to name a column "name"?
>UPDATE ABSENCES
>set CustomerContactID = cicntp.cnt_id
>from absences, cicntp
>where (SELECT cicntp.cnt_l_name
> FROM cicntp INNER JOIN
> gm_names ON cicntp.cnt_l_name = GM_NAMES.name INNER JOIN
> Absences ON cicntp.cnt_id = Absences.CustomerContactID)

Hi rdraider,

I guess that you made a copy & paste error, since this query can never
have reported over 230,000 rows affected - it can only report "Incorrect
syntax near ')'".

>Next I tried this query which is still running after 75 minutes (on a
>laptop)
>update absences
>set CustomerContactID = cicntp.cnt_id
>from absences, cicntp, gm_names
>where gm_names.name= cicntp.cnt_l_name

Depending on the size of the tables, it'll probably run a whole lot
longer - and it likely won't result in the change you wish. Since you
didn't specify anything to link absences to either of the other tables,
this query will effectively:

1. Join cicnt and gm_names on the name column, as specified in the WHERE
clause; then
2. Update EACH row in absences with the value of EACH row in the result
of the join above. If that join yields a million rows, then each row in
absences will be updated a million times. And in the end, the cnt_id
value from whatever row happens to be processed last will end up being
in CustomerContactID for ALL absences!

>As you can see, the 3 tables are ABSENCES, CICNTP and GM_NAMES.
>Absences.CustomerContactID is what I need updated, when finished it should
>match CICNTP.cnt_id
>GM_NAMES is a temp table and matches records in CICNTP.cnt_l_name
>Can some of you school this newbie on the best way to do this?
>Thanks a bunch!

Neither this narrative, nor any of the queries you posted does a very
good job at explaining what you need to get done. A much better way to
ask help in newsgroups is to post the table structure (as CREATE TABLE
statements), some rows of sample data (as INSERT statements) and the
expected output. More on this can be found at www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||1) Learn the Standard SQL UPDATE syntax, so you will not get Cartesian
explosions problems that will destroy your data integrity.

2) Avoid temp tables in favor of derived tables that the optimizer can
use.

3) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

4) Here is a wild guess. I would replace GM_names with a derived
table.

UPDATE Absenses
SET customer_contact_id
= (SELECT C1.cnt_id
FROM Cicntp AS C1, GM_names AS G1
WHERE C1.cnt_l_name = G1.name
AND C1.cnt_id = Absences.customer_contact_id)
WHERE EXISTS
(SELECT *
FROM Cicntp AS C1, GM_names AS G1
WHERE C1.cnt_l_name = G1.name
AND C1.cnt_id = Absences.customer_contact_id);

If the subquery expression returns no rows, you will get a NULL; If it
retursn more than one row, you will get a cardinality violation.|||On 15 Apr 2005 15:21:03 -0700, --CELKO-- wrote:

>4) Here is a wild guess. I would replace GM_names with a derived
>table.
>UPDATE Absenses
> SET customer_contact_id
> = (SELECT C1.cnt_id
> FROM Cicntp AS C1, GM_names AS G1
> WHERE C1.cnt_l_name = G1.name
> AND C1.cnt_id = Absences.customer_contact_id)
> WHERE EXISTS
> (SELECT *
> FROM Cicntp AS C1, GM_names AS G1
> WHERE C1.cnt_l_name = G1.name
> AND C1.cnt_id = Absences.customer_contact_id);

Hi Joe,

I was starting to post the exact same solution, but then I saw that this
version makes no sense at all - look at the way the subqueries are
joined to the table to be updated: the effect will be that
customer_contact_id will only be changed if it already has the correct
value. In other words: this UPDATE statement might affect some rows, but
it definitely won't change any data!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yes. But this is what he wrote when both of us (who are pretty good
SQL guys) trie to understand it. It would be nice to have some DDL and
clear specs instead of bad code ...