query

Can I have conatins clause like following
where contains(col_name ,'%')
where contains (col_name , '/*')
basically looking for special characters in text columnsHi.
Perhaps you want to look at this example
CREATE TABLE #TEST
(
COL VARCHAR(50)
)
INSERT INTO #TEST VALUES ('FF')
INSERT INTO #TEST VALUES ('F%F')
INSERT INTO #TEST VALUES ('FF%')
INSERT INTO #TEST VALUES ('NNFF')
INSERT INTO #TEST VALUES ('F88F')
INSERT INTO #TEST VALUES ('*FF')
SELECT * FROM #TEST WHERE CHARINDEX('%',COL)>0
<anonymous@.discussions.microsoft.com> wrote in message
news:085e01c39dfe$ff0d6db0$a501280a@.phx.gbl...
> Can I have conatins clause like following
>
> where contains(col_name ,'%')
> where contains (col_name , '/*')
> basically looking for special characters in text columns
>

queation about foreach loop

All,

I’m new to SSIS and have a question about foreach loop container.

Basically, I have 4 data flows and each of them has a variable that need be pass on to the next step, each of the variables will call a same stored procedure.

What should I use here? I guess I can use foreach loop container with the stored procedure inside of it, is that right? I tried all day yesterday and cannot make it work.

The foreach loop container did not call the procedure at all. Could someone kindly tell me how to do it?

If you are calling the same sproc 4 times, each time with different parameters then yes, the ForEach loop can be of help here.

You will need an execute SQL Task inside your ForEach loop container. The ForEach loop container itself doesn't actually call the sproc as you seem to think it will as suggested by the last line of your post.

-Jamie

|||

Jamie,

Thanks for your reply, Your blog helped me a lot. Thank you so much for putting such helpful information out there.

Back to my question, IF the foreach container is not supposed to call the Execute SQL task, then what should make it work?

Below is what I did, I don't know what i'm missing. I just cannot make it work!!!

*******************

ForeachLoop Editor:

Collection Tab:Foreach fromVariable Enumerator is the Enumerator

In Enumerator configuration, I created a new variable (V_EF) with the same datatype as the variable from the data flows

Variable Mappings tab: I added the 4 variables (come from each data flow) and the variable index are all 0

Expressions Table: I didn’t anything here because the procedure doesn’t return any value, what it does is update tables

Execute SQL Task Editor:

General Tab: ConnnectionType is OLEDB

SqlsourceType is Direct input

SQL statement isEXEC myProcedureName ?

bypassPrepare is false

Parameter Mapping: I use the variable (V_EF) I defined in Enumerator configuration

Result Set Tab: nothing here because the procedure will not return anything back

Expressions Tab: Nothing here

Many thanks!!

Jessie

|||please........|||

Jessie,

Under what circumstances do you not want to execute the Execute SQL Task?

Once you have answered that question then hopefully you can achieve what you need using expressions on your Precedence Constraints. Darren and Allan have a great demo of this here: http://www.sqlis.com/default.aspx?306

-Jamie

Qualifing table names with dbo

if dbo exists then the sql server resolves the object immeidately
otherwise it needs to search the databases one by one in the server.
Basically its a performance issue related.
HTH
Rajesh Peddireddy.
"Jim Abel" wrote:

> I see different examples of query statements some with the table names
> preceeded by dbo.tablename and others with only the table name. Does the
dbo
> do anything at all, especially if the user that request the query to execu
te
> is not the dbo simply a user with read only permissions?
>"Rajesh" <Rajesh@.discussions.microsoft.com> wrote in message
news:3931CB08-2AE8-4D0C-AC07-E5037BB45D92@.microsoft.com...
> if dbo exists then the sql server resolves the object immeidately
> otherwise it needs to search the databases one by one in the server.
> Basically its a performance issue related.
No it's not. SQL Server will never have to resolve the object name outside
of the database, and when it does this happens on at query comiliation time.
Once the query is compiled it should be reused, so this is not a performance
issue.
Prefixing with DBO is unnecesary and should be avoided in the case where all
the objects in a database reside in the DBO schema. In addition prefixing
is necessary to create schema-bound views and functions.
David|||David Browne (davidbaxterbrowne no potted meat@.hotmail.com) writes:
> Prefixing with DBO is unnecesary and should be avoided in the case where
> all the objects in a database reside in the DBO schema. In addition
> prefixing is necessary to create schema-bound views and functions.
I have had some discussions with the SQL Server team on that one...
In SQL 2000, if a plain user issues a SELECT statement or calls a
stored procedure with qualifying the name with dbo, there is a cost.
Say that user fred issues:
SELECT ... FROM tbl
EXEC some_sp
SQL Server will first have to check whether there is a fred.tbl or a
fred.some_sp. There is a cost for this.
In SQL 2005, this may be different. This is because in SQL 2005 owner
and schema are different. In SQL 2000 fred's default schema is "fred"
by necessity. In SQL 2005, fred may have dbo as his default schema, and
in this case, it should not matter whether you say tbl or dbo.tbl.
However, this depends on how the user fred was created. If the database
was carried over from SQL 2000, or ir the user was created with
sp_adduser out of habit, it will be as on SQL 2000.
It's a different issue inside of a stored procedure, and this is where I
don't agree with some of the SQL Server folks. In a stored procedure
owned by dbo, "SELECT ... FROM tbl" is unambiguous, and adding "dbo."
is just noise for the human reader. However, the SQL Server team
claims there still is a cost in this case. I tempted to say that in
such case this is a bug, but I have not looked at the actual code.
In any case, Microsoft's recommendation is that you should always
specify dbo. also inside stored procedures.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

qry wont use index

I've got a query which just won't use an index, instead, it does a table scan on a specific column. Basically the query is:

select count(*)
from table1 t1
inner join table2 t2 on (t1.myref = t2.myref)
where myint2 in (1,2,3)
and (myval between -1 and -2 or myint1 = 1)

In actual work, the 'myval between' uses variables which could be null, same goes for myint1. The values above are the values that I use to examine. According to the analyzer, a table scan is performed on myint2 (the in stuff), however, there's an index on myint2 also in combination with myint1.
I've tried to re-create the setup by creating the table1 and table2, including the indexes. Unfortunately, in the re-created setup, the index is used.

EDIT: Oddly, the OR ruins the plan to use an index: when leaving out the 'and (myval...)', the index is used.

What's going on?The first OR operation in a query (in this case, the IN clause) makes an index scan difficult. The second OR operation makes the index scan impossible (at least using present technology).

-PatP|||how's that? when changing
where myint2 in (1,2,3)
into
where myint2 = 1
I still get a table scan.

Besides, I do get an index-scan in the re-created setup.
I tried a defrag, reindex AND recompute statistics. It just wont show up.
I really don't get it.|||Hogtie the optimizer and try it. Use SET FORCEPLAN ON (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_8mni.asp) and hint the index. See if you get a result set while you are young enough to still care.

Let me know what you find out, I'm curious now!

-PatP|||hah! now your stuck! :>
the optimizer came up empty and even with the index hint (0) it still does a full table scan. The trouble is that the column resides in the facts-table (its a warehouse db) and in production it gets a 80% hit according to the exection plan (76% in dev). I'm beginning to wonder if the amount of indexes specified on the facts is too much (47 columns, 26 indexes).|||oh btw: it takes 14 minutes to come back with a rowcount of 85.
The forceplan option and index(0) hint does take the percentage down to 28% but it's still a table scan (forcedindex). btw: in the analyzer, execution plan, I've got these little round yellow circles on the tablescan, index, nested loops etc. I don't see 'em in BOL. Happen to know what their about?|||Unfortunately, nothing changed over the weekend.
I even changed the complete query to a simple count(*) with a single where-clause in which a single value is specified.
Still a full table scan, even with index-hint and forceplan set.