Hi,
Can i use 'Contains' with a ColumnName like
SELECT * FROM <Table_Name>
WHERE CONTAINS ( <ColumnName>, (SELECT <AnotherColumn> FROM
<anotherTable> ))
regards laraHi
I don't think you can do this, you may want to try something like:
SELECT T.col1
FROM Test1 t, Test2 s
WHERE T.col1 LIKE '%' + s.col1 + '%'
John
"Lara" wrote:
> Hi,
> Can i use 'Contains' with a ColumnName like
> SELECT * FROM <Table_Name>
> WHERE CONTAINS ( <ColumnName>, (SELECT <AnotherColumn> FROM
> <anotherTable> ))
> regards lara
>
>
Showing posts with label select. Show all posts
Showing posts with label select. Show all posts
Friday, March 23, 2012
Pb table scan
I have a query like this :
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn
04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a crit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
choice[vbcol=seagreen]
traceon,[vbcol=seagreen]
understand[vbcol=seagreen]
>
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn
04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a crit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
choice[vbcol=seagreen]
traceon,[vbcol=seagreen]
understand[vbcol=seagreen]
>
Pb table scan
I have a query like this :
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a écrit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
> > I have a query like this :
> >
> > select * from table
> > where champs1=val1 and champs2 =val2
> > I have an index on (champs1,champs2).
> > The table has 698694 rows.
> > The table scan cost 17770 i/os.
> >
> > If the number of rows selected is greater than 4700, the optimizer
choice
> > the table scan. If i force the index, i have 4762 i/os ( is less than
> > 17770 )
> >
> > I don't know why the optimizer choice the table scan ( is there a
traceon,
> > command like the dbcc traceon(302) in sybase, that help you to
understand
> > the choice of the optimizer ? ).
> >
> > Thanks and sorry for my poor english.
> >
> >
> >
> >
> >
> >
>
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a écrit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
> > I have a query like this :
> >
> > select * from table
> > where champs1=val1 and champs2 =val2
> > I have an index on (champs1,champs2).
> > The table has 698694 rows.
> > The table scan cost 17770 i/os.
> >
> > If the number of rows selected is greater than 4700, the optimizer
choice
> > the table scan. If i force the index, i have 4762 i/os ( is less than
> > 17770 )
> >
> > I don't know why the optimizer choice the table scan ( is there a
traceon,
> > command like the dbcc traceon(302) in sybase, that help you to
understand
> > the choice of the optimizer ? ).
> >
> > Thanks and sorry for my poor english.
> >
> >
> >
> >
> >
> >
>
Pb table scan
I have a query like this :
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.
Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a crit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
choice[vbcol=seagreen]
traceon,[vbcol=seagreen]
understand
>
sql
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.
Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a crit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
choice[vbcol=seagreen]
traceon,[vbcol=seagreen]
understand
>
sql
Wednesday, March 21, 2012
pb Order by
Under ASP, I have this statement which works:
strSQL="select * from " & Table_Name
objRS.open strSQL, objConn
But when I want to sort according to my column 'Position' (which is of number type under Access 2000) It no longer works. Any Idea?:
strSQL="select * from " & Table_Name & " Order by Position"
objRS.open strSQL, objConn
I have this error:
error '80004005'
Unspecified error
I'm connected this way:
StrConnect = "Provider=Microsoft.Jet.OLEDB.4.0;"&_
"Data Source=o:\teams\pursuit\db\productdb.mdb;" &_
"Persist Security Info=False"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = StrConnect
objConn.Open
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.activeconnection = objConn
Help:confused:Don't search, the name'position' for my field was a key word
strSQL="select * from " & Table_Name
objRS.open strSQL, objConn
But when I want to sort according to my column 'Position' (which is of number type under Access 2000) It no longer works. Any Idea?:
strSQL="select * from " & Table_Name & " Order by Position"
objRS.open strSQL, objConn
I have this error:
error '80004005'
Unspecified error
I'm connected this way:
StrConnect = "Provider=Microsoft.Jet.OLEDB.4.0;"&_
"Data Source=o:\teams\pursuit\db\productdb.mdb;" &_
"Persist Security Info=False"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = StrConnect
objConn.Open
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.activeconnection = objConn
Help:confused:Don't search, the name'position' for my field was a key word
Pattern matching with multiple values
I want to check for multiple patterns in a particular column.
For one pattern I can write e.g.
SELECT * FROM <TablName> WHERE ColumnName LIKE '%abcd%'.
My requirment is to select all rows for which column value matches with many patterns.I will fetch the patterns in a subquery
e.g. (SELECT '%'+name+'%' FROM <TableName>)
Any thoughts?
HiIt might work if you move your like into Exists , like
SELECT * FROM <TableName> T1
WHERE EXISTS (SELECT * FROM T2
WHERE T1.column LIKE '%' + T2.NAME + '%'
)
NB.|||
Thanks for your solution !
|||Thanks for your solution !
However my problem won't get solved this way. Actually I am building anSSRS report and the subquery was meant to be the 'Available Values ' of a multi-valued Input parameter.
When the user selects one or more params SRS will form a string of the values like " 'val1','val2','val3'...."
In my case SRS is forming a WHERE clause like
.........
.........
WHERE colname in ('val1','val2','val3'). This would search for the exact string, whereas I would like it to only match with patterns like '%val1%','%val2%','%val3%'.
i think you need to build it as
WHERE colname like '%val1%' or colname like '%val2%' or colname like '%val3%'.
|||Thnx. Shallu.....That is correct syntax, but you need to see that the number of values is not static and all of them are stored as one parameter by ssrs.
To simplify it for you, say SSRS is providing me with a string series like " '%val2%', '%val2%' ,'%val2%' ...". Now I need to use this to do my comparison.
|||HiThere might be a better solution from SSRS comunity , you should try your question there as well. From T_SQL point of view you could try following solution:
Create 2 store procedures : one - master that recived your string of parameters , parses a string into a list of single parameters and collects whatever children return , the other one - child , that can process one parameter at a time
CREATE PROCEDURE MultipleParameterSearch_procedure
@.intString varchar ()
as
CREATE TABLE #reultset (<your columns>)
DECLARE @.PARAMETER
-- parse your string into separate parameters , one at a time
...
...
WHILE @.PARAMETER is not nul
BEGIN
-- accumulate your single parameter procedure results
INSERT INTO #reultset (<your columns>)
EXEC SingleParameterSeach_Procedure @.PARAMETER
-- get next parameter
END
SELECT * FROM #reultset
RETURN
GO
CREATE PROCEDURE SingleParameterSeach_Procedure @.Parameter VARCHAR()
AS
SELECT <your columns>FROM <Table> WHERE Column LIKE '%' + @.Parameter + '%'
RETURN
GO|||Hi
Just one last suggestion.
LIKE %val%' is a very expensive operation, as no indexes can be used to help QueryOptimiser to make a quick search. Adding multiple LIKE parameters are going to decrease performance of your query. If you are doing your Report for production , explain to the user implications and pesweid them to use a single-parameter select .
:-).NB|||Thanks for taking the pains .I'll follow this up 
Please take a look at the link below:
http://www.sommarskog.se/arrays-in-sql.html
It discusses various techniques to process lists using SQL. You can use one of those methods to generate a table that contains the individual values and then use it as source in the EXISTS sub-query.
Monday, March 12, 2012
patch null value?
following is my sql:
select a.dcode, b.district from table1 a, table2 b
where
a.id = b.id
and return following result:
dcode district
123 south
321 north
456 east
789 west
123
789
so for those records that district are null, how can i fill them up as i know they have the same dcode as the other records?
use Isnull or coalesce
Code Snippet
select a.dcode, isnull(b.district,-1) from table1 a, table2 b
where
a.id = b.id
--or
select a.dcode, COALESCE(b.district,-1) from table1 a, table2 b
where
a.id = b.id
|||This could possibly point towards a de-normalized database design.
It might be better if you created a separate lookup table to store a distinct list of 'dcode' values against the appropriate 'district' value (and possibly even split distinct 'district' values out to a second lookup table) - you could then store 'dcode' in both table1 and table2 and remove the 'district' column from table2. Depending on your database design there may even be scope to store 'dcode' in either table1 or table2 (and not both).
Granted, you might have to re-work substantial amounts of code but it would help you in situations such as the one you're currently facing and would, more importantly, help to maintain the integrity of your data.
Chris
|||Joe,
Below is one way that you could accomplish this.
declare @.temptable table
(dcode int,
district varchar(20))
insert into @.temptable values (123,'south')
insert into @.temptable values (321,'north')
insert into @.temptable values (456,'east')
insert into @.temptable values (789,'west')
insert into @.temptable values (123,NULL)
insert into @.temptable values (789,NULL)
select a.dcode,COALESCE(a.district,b.district,'')
from @.temptable a
left join (select dcode,district
from @.temptable
where district is not null) b
on a.dcode = b.dcode
Let me know if you have any other questions.
crusso.
|||I completely agree with Chris that you have an improperly desiged database.
First thing: You need to create a District table that has a district code/Id and description, and use this code in both of your tables. This fixes the inconsistency of codes.
Second: Why are you recording district in both tables? (your names give no clue) Does this district mean the same thing? Or could the be different for some logical reason? If they have the same meaning, then delete one and join to get the other one if you need it in a query. This will make sure that you don't get out of sync values.
|||
i've figured myself how to solve this problem:
select a.dcode, b.district into #temp from table1 a, table2 b
where
a.id = b.id
I
I
I
V
update #tempset district = b.district
from #temp a, (select * from #temp) b
where
a.dcode = b.dcode
and
a.district is null
patch null value?
following is my sql:
select a.dcode, b.district from table1 a, table2 b
where
a.id = b.id
and return following result:
dcode district
123 south
321 north
456 east
789 west
123
789
so for those records that district are null, how can i fill them up as i know they have the same dcode as the other records?
use Isnull or coalesce
Code Snippet
select a.dcode, isnull(b.district,-1) from table1 a, table2 b
where
a.id = b.id
--or
select a.dcode, COALESCE(b.district,-1) from table1 a, table2 b
where
a.id = b.id
|||This could possibly point towards a de-normalized database design.
It might be better if you created a separate lookup table to store a distinct list of 'dcode' values against the appropriate 'district' value (and possibly even split distinct 'district' values out to a second lookup table) - you could then store 'dcode' in both table1 and table2 and remove the 'district' column from table2. Depending on your database design there may even be scope to store 'dcode' in either table1 or table2 (and not both).
Granted, you might have to re-work substantial amounts of code but it would help you in situations such as the one you're currently facing and would, more importantly, help to maintain the integrity of your data.
Chris
|||Joe,
Below is one way that you could accomplish this.
declare @.temptable table
(dcode int,
district varchar(20))
insert into @.temptable values (123,'south')
insert into @.temptable values (321,'north')
insert into @.temptable values (456,'east')
insert into @.temptable values (789,'west')
insert into @.temptable values (123,NULL)
insert into @.temptable values (789,NULL)
select a.dcode,COALESCE(a.district,b.district,'')
from @.temptable a
left join (select dcode,district
from @.temptable
where district is not null) b
on a.dcode = b.dcode
Let me know if you have any other questions.
crusso.
|||I completely agree with Chris that you have an improperly desiged database.
First thing: You need to create a District table that has a district code/Id and description, and use this code in both of your tables. This fixes the inconsistency of codes.
Second: Why are you recording district in both tables? (your names give no clue) Does this district mean the same thing? Or could the be different for some logical reason? If they have the same meaning, then delete one and join to get the other one if you need it in a query. This will make sure that you don't get out of sync values.
|||i've figured myself how to solve this problem:
select a.dcode, b.district into #temp from table1 a, table2 b
where
a.id = b.id
I
I
I
V
update #tempset district = b.district
from #temp a, (select * from #temp) b
where
a.dcode = b.dcode
and
a.district is null
patch null value?
following is my sql:
select a.dcode, b.district from table1 a, table2 b
where
a.id = b.id
and return following result:
dcode district
123 south
321 north
456 east
789 west
123
789
so for those records that district are null, how can i fill them up as i know they have the same dcode as the other records?
use Isnull or coalesce
Code Snippet
select a.dcode, isnull(b.district,-1) from table1 a, table2 b
where
a.id = b.id
--or
select a.dcode, COALESCE(b.district,-1) from table1 a, table2 b
where
a.id = b.id
|||This could possibly point towards a de-normalized database design.
It might be better if you created a separate lookup table to store a distinct list of 'dcode' values against the appropriate 'district' value (and possibly even split distinct 'district' values out to a second lookup table) - you could then store 'dcode' in both table1 and table2 and remove the 'district' column from table2. Depending on your database design there may even be scope to store 'dcode' in either table1 or table2 (and not both).
Granted, you might have to re-work substantial amounts of code but it would help you in situations such as the one you're currently facing and would, more importantly, help to maintain the integrity of your data.
Chris
|||Joe,
Below is one way that you could accomplish this.
declare @.temptable table
(dcode int,
district varchar(20))
insert into @.temptable values (123,'south')
insert into @.temptable values (321,'north')
insert into @.temptable values (456,'east')
insert into @.temptable values (789,'west')
insert into @.temptable values (123,NULL)
insert into @.temptable values (789,NULL)
select a.dcode,COALESCE(a.district,b.district,'')
from @.temptable a
left join (select dcode,district
from @.temptable
where district is not null) b
on a.dcode = b.dcode
Let me know if you have any other questions.
crusso.
|||I completely agree with Chris that you have an improperly desiged database.
First thing: You need to create a District table that has a district code/Id and description, and use this code in both of your tables. This fixes the inconsistency of codes.
Second: Why are you recording district in both tables? (your names give no clue) Does this district mean the same thing? Or could the be different for some logical reason? If they have the same meaning, then delete one and join to get the other one if you need it in a query. This will make sure that you don't get out of sync values.
|||
i've figured myself how to solve this problem:
select a.dcode, b.district into #temp from table1 a, table2 b
where
a.id = b.id
I
I
I
V
update #tempset district = b.district
from #temp a, (select * from #temp) b
where
a.dcode = b.dcode
and
a.district is null
Friday, March 9, 2012
Password problem
Hi to all.
I'm trying to add a new login to SQL Server Express, using SQL Management Studio...
I create the user, select "SQL Server authentication", and type the password, wich is strong enough. I click OK, and, for now, everything's fine. The problem arises when I access that login's properties: the password is different (it has different length), so I can't login with that account.
Any help would be greatly appreciated...
Thanks,
Alberto.
Hi,the length of the password doesn′t have to do anything with the password itself, even if you put in a blank password (if this is made possible) you will get a bunch of stars / points presented there. That is just a hint that in there is a secret (password) stored in there.
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||
mmhh...it seems reasonable (with the help of some kind of weird logic)...thanks a lot!
Wednesday, March 7, 2012
Password Field in SQL Server
If i would like to have the field to store password(data, ****), what type
of field in sql server?
is it good to select binary?Storing passwords is an inherently insecure practice that would
probably fall foul of a security audit (a good audit anyway). If you
must roll your own security then store a salted, secure hash of the
password. For that BINARY is the obvious choice.
--
David Portas
SQL Server MVP
--|||Implement your own en/decryptring methody in your middle tier to
accomplish this.
HTH, jens Suessmeyer.
of field in sql server?
is it good to select binary?Storing passwords is an inherently insecure practice that would
probably fall foul of a security audit (a good audit anyway). If you
must roll your own security then store a salted, secure hash of the
password. For that BINARY is the obvious choice.
--
David Portas
SQL Server MVP
--|||Implement your own en/decryptring methody in your middle tier to
accomplish this.
HTH, jens Suessmeyer.
Password Field in SQL Server
If i would like to have the field to store password(data, ****), what type
of field in sql server?
is it good to select binary?
Storing passwords is an inherently insecure practice that would
probably fall foul of a security audit (a good audit anyway). If you
must roll your own security then store a salted, secure hash of the
password. For that BINARY is the obvious choice.
David Portas
SQL Server MVP
|||Implement your own en/decryptring methody in your middle tier to
accomplish this.
HTH, jens Suessmeyer.
of field in sql server?
is it good to select binary?
Storing passwords is an inherently insecure practice that would
probably fall foul of a security audit (a good audit anyway). If you
must roll your own security then store a salted, secure hash of the
password. For that BINARY is the obvious choice.
David Portas
SQL Server MVP
|||Implement your own en/decryptring methody in your middle tier to
accomplish this.
HTH, jens Suessmeyer.
Password Field in SQL Server
If i would like to have the field to store password(data, ****), what type
of field in sql server?
is it good to select binary?Storing passwords is an inherently insecure practice that would
probably fall foul of a security audit (a good audit anyway). If you
must roll your own security then store a salted, secure hash of the
password. For that BINARY is the obvious choice.
David Portas
SQL Server MVP
--|||Implement your own en/decryptring methody in your middle tier to
accomplish this.
HTH, jens Suessmeyer.
of field in sql server?
is it good to select binary?Storing passwords is an inherently insecure practice that would
probably fall foul of a security audit (a good audit anyway). If you
must roll your own security then store a salted, secure hash of the
password. For that BINARY is the obvious choice.
David Portas
SQL Server MVP
--|||Implement your own en/decryptring methody in your middle tier to
accomplish this.
HTH, jens Suessmeyer.
Saturday, February 25, 2012
Password Audit
Hello All,
I am in the process of auditing a SQL server and was checking for NULL
passwords. I ran the following query select name, password from
master..sysxlogins where password is NULL.
It returned the expected windows accounts with NULL passwords, but it also
returned two unknown accounts with NULL for the SID, Name, and Password, and
a status of 192.
Has anyone ever seen this before, and if so, what they might be?
Thanks
DanHi,
Have you tried with C2 Audit ? Password Audit from www.sqlsecurity.com ?
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
I am in the process of auditing a SQL server and was checking for NULL
passwords. I ran the following query select name, password from
master..sysxlogins where password is NULL.
It returned the expected windows accounts with NULL passwords, but it also
returned two unknown accounts with NULL for the SID, Name, and Password, and
a status of 192.
Has anyone ever seen this before, and if so, what they might be?
Thanks
DanHi,
Have you tried with C2 Audit ? Password Audit from www.sqlsecurity.com ?
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
Monday, February 20, 2012
passing table name and order by parameter to stored procedure
can i pass the name of the table and the "order by" column name to stored procedure?
i tried the simple way
(@.tablename varchar and then "select * from @.tablename)
but i get error massesges. the same for order by...
what is the right syntex for this task?
You have to use dynamic SQL for this task, unfortunately. You have to create a character string in your sproc that puts together the SQL statement and then you have to call exec(@.sql) on it.
Example:
@.sql = 'SELECT * FROM ' + @.tableName
exec(@.sql)
|||well, i just started to use stored procedure, i think that i will leave dynamic sql to later on...
passing SELECT result to OS
In Windows, I have a folder, lets say 'ABC'. I'd like this folder to be
renamed only a daily basis, based the current datestamp, through T-SQL. So
far, I've come up the following T-SQL stmt:
SELECT RTRIM(LEFT(DATENAME(month, getdate()),3)+DATENAME(day,
getdate())+DATENAME(year, getdate()))
...How would I be able to implement the result of the above stament (in the
case, Feb232006) to be the new folder name, i.e., take the result of the SQL
select stmt. and pass it to the OS to rename a folder?
Thanks.DECLARE @.cmd VARCHAR(255);
SET @.cmd = 'ren "d:\abc\" "foo"';
EXEC master..xp_cmdshell @.cmd, NO_OUTPUT;
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:D2B570A3-A31A-4C80-A5E2-44ABD38E52BE@.microsoft.com...
> In Windows, I have a folder, lets say 'ABC'. I'd like this folder to be
> renamed only a daily basis, based the current datestamp, through T-SQL. So
> far, I've come up the following T-SQL stmt:
> SELECT RTRIM(LEFT(DATENAME(month, getdate()),3)+DATENAME(day,
> getdate())+DATENAME(year, getdate()))
> ...How would I be able to implement the result of the above stament (in
> the
> case, Feb232006) to be the new folder name, i.e., take the result of the
> SQL
> select stmt. and pass it to the OS to rename a folder?
> Thanks.|||"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:D2B570A3-A31A-4C80-A5E2-44ABD38E52BE@.microsoft.com...
> In Windows, I have a folder, lets say 'ABC'. I'd like this folder to be
> renamed only a daily basis, based the current datestamp, through T-SQL. So
> far, I've come up the following T-SQL stmt:
> SELECT RTRIM(LEFT(DATENAME(month, getdate()),3)+DATENAME(day,
> getdate())+DATENAME(year, getdate()))
> ...How would I be able to implement the result of the above stament (in
> the
> case, Feb232006) to be the new folder name, i.e., take the result of the
> SQL
> select stmt. and pass it to the OS to rename a folder?
> Thanks.
Lookup xp_cmdshell in Books Online. Why do it in SQL though?
Your naming convention for folders looks very inconvenient. The user will
see the months sorted alphabetically in Explorer instead of chronologically.
The years will be mixed up too! Better to do:
\2006_02_23
\2006_02_24
\... etc
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||It's okay... I figured it out myself by using the following code:
declare @.date varchar(10)
declare @.cmd varchar(250)
set @.date = RTRIM(LEFT(DATENAME(month, getdate()),3)+DATENAME(day,
getdate())+DATENAME(year, getdate()))
Print @.date
set @.cmd = 'REN X:\ABC ' + @.date
Print @.cmd
"Rob" wrote:
> In Windows, I have a folder, lets say 'ABC'. I'd like this folder to be
> renamed only a daily basis, based the current datestamp, through T-SQL. So
> far, I've come up the following T-SQL stmt:
> SELECT RTRIM(LEFT(DATENAME(month, getdate()),3)+DATENAME(day,
> getdate())+DATENAME(year, getdate()))
> ...How would I be able to implement the result of the above stament (in th
e
> case, Feb232006) to be the new folder name, i.e., take the result of the S
QL
> select stmt. and pass it to the OS to rename a folder?
> Thanks.
renamed only a daily basis, based the current datestamp, through T-SQL. So
far, I've come up the following T-SQL stmt:
SELECT RTRIM(LEFT(DATENAME(month, getdate()),3)+DATENAME(day,
getdate())+DATENAME(year, getdate()))
...How would I be able to implement the result of the above stament (in the
case, Feb232006) to be the new folder name, i.e., take the result of the SQL
select stmt. and pass it to the OS to rename a folder?
Thanks.DECLARE @.cmd VARCHAR(255);
SET @.cmd = 'ren "d:\abc\" "foo"';
EXEC master..xp_cmdshell @.cmd, NO_OUTPUT;
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:D2B570A3-A31A-4C80-A5E2-44ABD38E52BE@.microsoft.com...
> In Windows, I have a folder, lets say 'ABC'. I'd like this folder to be
> renamed only a daily basis, based the current datestamp, through T-SQL. So
> far, I've come up the following T-SQL stmt:
> SELECT RTRIM(LEFT(DATENAME(month, getdate()),3)+DATENAME(day,
> getdate())+DATENAME(year, getdate()))
> ...How would I be able to implement the result of the above stament (in
> the
> case, Feb232006) to be the new folder name, i.e., take the result of the
> SQL
> select stmt. and pass it to the OS to rename a folder?
> Thanks.|||"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:D2B570A3-A31A-4C80-A5E2-44ABD38E52BE@.microsoft.com...
> In Windows, I have a folder, lets say 'ABC'. I'd like this folder to be
> renamed only a daily basis, based the current datestamp, through T-SQL. So
> far, I've come up the following T-SQL stmt:
> SELECT RTRIM(LEFT(DATENAME(month, getdate()),3)+DATENAME(day,
> getdate())+DATENAME(year, getdate()))
> ...How would I be able to implement the result of the above stament (in
> the
> case, Feb232006) to be the new folder name, i.e., take the result of the
> SQL
> select stmt. and pass it to the OS to rename a folder?
> Thanks.
Lookup xp_cmdshell in Books Online. Why do it in SQL though?
Your naming convention for folders looks very inconvenient. The user will
see the months sorted alphabetically in Explorer instead of chronologically.
The years will be mixed up too! Better to do:
\2006_02_23
\2006_02_24
\... etc
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||It's okay... I figured it out myself by using the following code:
declare @.date varchar(10)
declare @.cmd varchar(250)
set @.date = RTRIM(LEFT(DATENAME(month, getdate()),3)+DATENAME(day,
getdate())+DATENAME(year, getdate()))
Print @.date
set @.cmd = 'REN X:\ABC ' + @.date
Print @.cmd
"Rob" wrote:
> In Windows, I have a folder, lets say 'ABC'. I'd like this folder to be
> renamed only a daily basis, based the current datestamp, through T-SQL. So
> far, I've come up the following T-SQL stmt:
> SELECT RTRIM(LEFT(DATENAME(month, getdate()),3)+DATENAME(day,
> getdate())+DATENAME(year, getdate()))
> ...How would I be able to implement the result of the above stament (in th
e
> case, Feb232006) to be the new folder name, i.e., take the result of the S
QL
> select stmt. and pass it to the OS to rename a folder?
> Thanks.
Passing SELECT ALL from URL
Is there a way to pass the "select all" option to a multi-select parameter from a URL string?
Thanks, Eva
Hi,
In the case of multi-select with Select All let's assume 2 cases, a text param and an INT param.
Assuming no default values are set
INT param.
Set the report parameter like that
Param Value, Label = Select All, Value = 2147483647
Set the procedure like that, parameter declaration
@.Value as int = 2147483647
Set the procedure Limit like that
AND ( @.Value = 2147483647 or i.Value <= @.Value)
Then call the URL Like that
http://myname.mydomain.com/ReportServer/Pages/ReportViewer.aspx?%2fNew+Product+Market+Synergy+Reports%2fNP+Market+Synergy&rs%3aCommand=Render&Division=%23%23&project=1763570219&Value=2147483647&OptyDetailsTop=10&rs:format=EXCEL
For Strings use something like
Param Division, Label = All, Value = '##'
or use a dataset for your param like that
Select '##' as Value, '*All' As label
union all
Select distinct
Division as Value, Division_Description as Label from tb_Division
order by Label
in the proc use this param declaration
@.Division as Varchar(85) = '##'
And the limit like that
where ( @.Division = '##' or i2.Division = @.Division)
Then call it like that from the URL
http://myname.mydomain.com/ReportServer/Pages/ReportViewer.aspx?%2fNew+Product+Market+Synergy+Reports%2fNP+Market+Synergy&rs%3aCommand=Render&Division=%23%23&project=1763570219&Value=2147483647&OptyDetailsTop=10&rs:format=PDF
Where %23%23 will pass the ## to the procedure parameter.
Note that if you simply want to display the report, remove the &rs:format tag from the URL.
Since the procedure limit start with @.Division = '##' the i2.Division = @.Division will not even be part of the query sent to the server.
Regards,
Philippe
|||If I do something like you suggest RS puts the Select ALL option at the top of the multi-select parameter. Selecting both the RS select all option and the one I built in makes the queries not work. Therefore, I really just want to leave the datasets clean and the parameters in my main query TableField in (@.param) and just be able to send the URL something that tells it I want that default RS Select ALL option as if I had set that thing as the default value of the parameter.|||
Oops sorry, the example where I craft the select all was for a single select or a pre-SP2 install.
For a multi-select with built-in Select all you would actually just use the standard list of values. here the trick to avoid a huge Where Item is IN ('a', 'b', 'c',......) would be to do it like this
Procedure parameter
, @.Item varchar(Max) = '##'
Procedure handling of ALL option.
declare @.NKeyCount as int
declare @.NKeyMax as int
set @.NKeyCount = onglobals.dbo.fn_CountChar(@.Item, ',')
set @.NKeyMax = (
Select Count(Distinct b.Item_Cd) as Value
from meta_NewProductBaseline b
)
if @.NKeyCount + 1 = @.NKeyMax begin set @.Item = '##' end
If @.Item is null begin set @.Item = '##' end
Procedure limit
And (@.Item = '##' or b.Item_Cd in (select ltrim(SQLstr) from ONGlobals.dbo.clrfn_SplitCommaDelimitedString(@.Item) ) )
Unfortunately, you need a bunch of functions to get it to work. I hope you already have those functions. If not, you can try to find it from this forum or I can give you the code.
Philippe
|||OK, Here is the code for the 2 functions. one is SQL the other is CLR.
You do not have to use CLR, there are multiple other ways to do this.
See
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=399&messageid=386759
Also, note that the CLR I show here is limited to 4000 chars and miss a build-in LTRIM...
Count chars
Code Snippet
CREATE FUNCTION dbo.fn_countchar (@.source varchar(max), @.charval varchar(255))
returns int
as
BEGIN
DECLARE @.len int, @.icount int, @.count int
SET @.len = len(@.source)
SET @.icount = 1
SET @.count = 0
WHILE @.icount<= @.len
BEGIN
IF substring(@.source, @.icount, 1) = @.charval
SET @.count = @.count + 1
SET @.icount = @.icount +1
END
RETURN @.count
end
Split multi-param
Code Snippet
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true, TableDefinition = "SQLStr nvarchar(4000)", FillRowMethodName = "FillSplitCommaDelimitedStringToStr")]
public static System.Collections.IEnumerable clrfn_SplitCommaDelimitedString(SqlString str)
{
string x = str.Value;
if (!string.IsNullOrEmpty(x))
{
return x.Split(',');
}
else
{
return null;
}
}
private static void FillSplitCommaDelimitedStringToStr(object obj, out SqlString str)
{
if (obj != null)
str = (String)(obj);
else
str = String.Empty;
}
};
|||How do I pass this any values from a URL string?
|||SAme as previously posted. You call the report via URL and the report will call the procedure.
http://myname.mydomain.com/ReportServer/Pages/ReportViewer.aspx?%2fNew+Product+Market+Synergy+Reports%2fNP+Market+Synergy&rs%3aCommand=Render&Division=%23%23&project=1763570219&Value=2147483647&OptyDetailsTop=10&rs:format=EXCEL
Use %23%23 to pass ## meaning ALL otherwhise pass the standard list of values for a multiselect or just select all in the multiselect.
Philippe
Passing SELECT ALL from URL
Is there a way to pass the "select all" option to a multi-select parameter from a URL string?
Thanks, Eva
Hi,
In the case of multi-select with Select All let's assume 2 cases, a text param and an INT param.
Assuming no default values are set
INT param.
Set the report parameter like that
Param Value, Label = Select All, Value = 2147483647
Set the procedure like that, parameter declaration
@.Value as int = 2147483647
Set the procedure Limit like that
AND ( @.Value = 2147483647 or i.Value <= @.Value)
Then call the URL Like that
http://myname.mydomain.com/ReportServer/Pages/ReportViewer.aspx?%2fNew+Product+Market+Synergy+Reports%2fNP+Market+Synergy&rs%3aCommand=Render&Division=%23%23&project=1763570219&Value=2147483647&OptyDetailsTop=10&rs:format=EXCEL
For Strings use something like
Param Division, Label = All, Value = '##'
or use a dataset for your param like that
Select '##' as Value, '*All' As label
union all
Select distinct
Division as Value, Division_Description as Label from tb_Division
order by Label
in the proc use this param declaration
@.Division as Varchar(85) = '##'
And the limit like that
where ( @.Division = '##' or i2.Division = @.Division)
Then call it like that from the URL
http://myname.mydomain.com/ReportServer/Pages/ReportViewer.aspx?%2fNew+Product+Market+Synergy+Reports%2fNP+Market+Synergy&rs%3aCommand=Render&Division=%23%23&project=1763570219&Value=2147483647&OptyDetailsTop=10&rs:format=PDF
Where %23%23 will pass the ## to the procedure parameter.
Note that if you simply want to display the report, remove the &rs:format tag from the URL.
Since the procedure limit start with @.Division = '##' the i2.Division = @.Division will not even be part of the query sent to the server.
Regards,
Philippe
|||If I do something like you suggest RS puts the Select ALL option at the top of the multi-select parameter. Selecting both the RS select all option and the one I built in makes the queries not work. Therefore, I really just want to leave the datasets clean and the parameters in my main query TableField in (@.param) and just be able to send the URL something that tells it I want that default RS Select ALL option as if I had set that thing as the default value of the parameter.|||
Oops sorry, the example where I craft the select all was for a single select or a pre-SP2 install.
For a multi-select with built-in Select all you would actually just use the standard list of values. here the trick to avoid a huge Where Item is IN ('a', 'b', 'c',......) would be to do it like this
Procedure parameter
, @.Item varchar(Max) = '##'
Procedure handling of ALL option.
declare @.NKeyCount as int
declare @.NKeyMax as int
set @.NKeyCount = onglobals.dbo.fn_CountChar(@.Item, ',')
set @.NKeyMax = (
Select Count(Distinct b.Item_Cd) as Value
from meta_NewProductBaseline b
)
if @.NKeyCount + 1 = @.NKeyMax begin set @.Item = '##' end
If @.Item is null begin set @.Item = '##' end
Procedure limit
And (@.Item = '##' or b.Item_Cd in (select ltrim(SQLstr) from ONGlobals.dbo.clrfn_SplitCommaDelimitedString(@.Item) ) )
Unfortunately, you need a bunch of functions to get it to work. I hope you already have those functions. If not, you can try to find it from this forum or I can give you the code.
Philippe
|||OK, Here is the code for the 2 functions. one is SQL the other is CLR.
You do not have to use CLR, there are multiple other ways to do this.
See
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=399&messageid=386759
Also, note that the CLR I show here is limited to 4000 chars and miss a build-in LTRIM...
Count chars
Code Snippet
CREATE FUNCTION dbo.fn_countchar (@.source varchar(max), @.charval varchar(255))
returns int
as
BEGIN
DECLARE @.len int, @.icount int, @.count int
SET @.len = len(@.source)
SET @.icount = 1
SET @.count = 0
WHILE @.icount<= @.len
BEGIN
IF substring(@.source, @.icount, 1) = @.charval
SET @.count = @.count + 1
SET @.icount = @.icount +1
END
RETURN @.count
end
Split multi-param
Code Snippet
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true, TableDefinition = "SQLStr nvarchar(4000)", FillRowMethodName = "FillSplitCommaDelimitedStringToStr")]
public static System.Collections.IEnumerable clrfn_SplitCommaDelimitedString(SqlString str)
{
string x = str.Value;
if (!string.IsNullOrEmpty(x))
{
return x.Split(',');
}
else
{
return null;
}
}
private static void FillSplitCommaDelimitedStringToStr(object obj, out SqlString str)
{
if (obj != null)
str = (String)(obj);
else
str = String.Empty;
}
};
|||How do I pass this any values from a URL string?
|||SAme as previously posted. You call the report via URL and the report will call the procedure.
http://myname.mydomain.com/ReportServer/Pages/ReportViewer.aspx?%2fNew+Product+Market+Synergy+Reports%2fNP+Market+Synergy&rs%3aCommand=Render&Division=%23%23&project=1763570219&Value=2147483647&OptyDetailsTop=10&rs:format=EXCEL
Use %23%23 to pass ## meaning ALL otherwhise pass the standard list of values for a multiselect or just select all in the multiselect.
Philippe
Passing results from select into exec
Hi
I'm trying to call a stored proc from within another of my stored
procs, passing the results of a select statement as the parameters -
but it's not working. Here's what I'm trying to do:
CREATE PROCEDURE [MoveRecordsToArchive]
AS
EXEC [ARCHIVEInsert]
(
SELECT
*
FROM
[CURRENT_DATA]
)
where [ARCHIVEInsert] is a stored proc which takes parameters which are
the same as the columns in [CURRENT_DATA].
But I'm getting the following error when trying to run
MoveRecordsToArchive:
"Procedure 'MoveRecordsToArchive' expects parameter '@.ID', which was
not supplied.", where @.ID is the first parameter to [ARCHIVEInsert] and
[ID] is the first column in [CURRENT_DATA].
Am I trying to do something impossible again? If so, how else could I
go about this?You will have to do it like this
declare @.ID' int @.name varchar(50)
select @.id =id,@.name =name
from [CURRENT_DATA] where id = Somevalue
exec ARCHIVEInsert @.id ,@.name
keep in mind you can only use 1 row at a time
You can do this if you need to move more rows
INSERT INTO DATA_ARCHIVE
SELECT * FROM [CURRENT_DATA]
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Unfortunately, there may well be many rows to move, and I can't just
use your second method (i.e. a straight INSERT) since I need to insert
if the row doesn't exist in the archive table, and update if it does,
which is the process that the ARCHIVEInsert sp follows.
Is there then an easy way to iterate through all the rows in the table
so that I can use your first method?|||The insert
INSERT INTO DATA_ARCHIVE
SELECT * FROM [CURRENT_DATA] c
left join DATA_ARCHIVE d on c.id =d.id
where d.id is null
The update
update d set d.field1 = c.field1,d.field2 = c.field2,etc,etc,etc
FROM [CURRENT_DATA] c
join DATA_ARCHIVE d on c.id =d.id
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Yeah, that's not quite what I need. As I say, I have the
insert-or-update functionality working, in my ARCHIVEInsert storedproc.
I guess I could duplicate in the MoveRowsToArchive proc, but it seems
a bit silly when I have a perfectly good stored proc to call that
already does what I need.
Thanks anyway.
SQL wrote:
> The insert
> INSERT INTO DATA_ARCHIVE
> SELECT * FROM [CURRENT_DATA] c
> left join DATA_ARCHIVE d on c.id =d.id
> where d.id is null
> The update
> update d set d.field1 = c.field1,d.field2 = c.field2,etc,etc,etc
> FROM [CURRENT_DATA] c
> join DATA_ARCHIVE d on c.id =d.id
> ----
--
> "I sense many useless updates in you... Useless updates lead to
> fragmentation... Fragmentation leads to downtime...Downtime leads to
> suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
> and DBCC DBREINDEX are the force...May the force be with you" --
> http://sqlservercode.blogspot.com/|||"Cathryn Johns" <cjohns@.gmail.com> wrote in message
news:1130510060.880509.220720@.f14g2000cwb.googlegroups.com...
> Yeah, that's not quite what I need. As I say, I have the
> insert-or-update functionality working, in my ARCHIVEInsert storedproc.
> I guess I could duplicate in the MoveRowsToArchive proc, but it seems
> a bit silly when I have a perfectly good stored proc to call that
> already does what I need.
> Thanks anyway.
> SQL wrote:
>
Well, if you want to do it the hard way..
Create a CURSOR in the first stored procedure and pull one row at a time and
call your insert/update spoc in a loop.
Something like:
declare @.ID' int @.name varchar(50)
DECLARE Cur CURSOR FOR
select id, name
from [CURRENT_DATA] where id = Somevalue
OPEN Cur
FETCH NEXT FROM Cur
INTO @.ID, @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec ARCHIVEInsert @.id ,@.name
FETCH NEXT FROM Cur
INTO @.ID, @.name
END
CLOSE Cur
DEALLOCATE Cur|||Cathryn Johns (cjohns@.gmail.com) writes:
> Yeah, that's not quite what I need. As I say, I have the
> insert-or-update functionality working, in my ARCHIVEInsert storedproc.
> I guess I could duplicate in the MoveRowsToArchive proc, but it seems
> a bit silly when I have a perfectly good stored proc to call that
> already does what I need.
That's not really right. You have a stored procedure which has the
logic to do this for one single row. Now you need something that performs
the same thing for multiple rows.
You can of coruse iterate over the source table, and move one row at a
time. But, frankly, for simple logic like this, that would be about
criminal. OK, that choice of words may stun you, but if I tell you
that to move 10000 rows, it would take 50 seconds with calling the
stored procedure for each row, and five seconds with the solution
that Denis posted, you may agree. Of course, I just made those numbers
up, but the difference is really that drastic - or even worse, if
proper indexing is not in place.
RDBMS are designed to work with sets of data, and you should try to
this as much as possible.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Cathryn Johns (cjohns@.gmail.com) writes:
> That's not really right. You have a stored procedure which has the
> logic to do this for one single row. Now you need something that performs
> the same thing for multiple rows.
> You can of coruse iterate over the source table, and move one row at a
> time. But, frankly, for simple logic like this, that would be about
> criminal. OK, that choice of words may stun you, but if I tell you
> that to move 10000 rows, it would take 50 seconds with calling the
> stored procedure for each row, and five seconds with the solution
> that Denis posted, you may agree. Of course, I just made those numbers
> up, but the difference is really that drastic - or even worse, if
> proper indexing is not in place.
> RDBMS are designed to work with sets of data, and you should try to
> this as much as possible.
Okay, I see what you're saying, but please bear with me here because I
know I don't have the right sql mindset :-) - I'm more used to regular
functional programming.
I understand the update & insert as posted by SQL, but what I don't get
is how to determine which rows need to be inserted and which need to be
updated *without* going through each row, one by one. Currently my
table has a uniqueness constraint consisting of several columns, and
what my stored proc does is try to insert (since this will happen
successfully 99% of the time, I try the insert first), then checks
@.@.error and if the code indicates a uniqueness constraint violation, it
updates instead. Maybe this isn't the best way to implement what I'm
trying to accomplish, but either way I still don't see how to do this
using a set-based approach. The cursor approach makes more sense to
me, but I can see that it could be really slow.|||Cathryn,
"I understand the update & insert as posted by SQL, but what I don't
get
is how to determine which rows need to be inserted and which need to be
updated *without* going through each row, one by one"
when you do a join only rows that exist in both tables are returned so
you can do an update
when you do a left join with where d.id is null only the rows that
don't exist in the other table are returned so you can do an update
When you work with SQL you have to think in terms of sets, basically
you have to unlearn what you were taught for VB, Java C# etc etc
A very good book on T-SQL is
The Guru's Guide to Transact-SQL by Ken Henderson
http://www.amazon.com/exec/obidos/t...=glance&s=books
And of course Books On Line
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Cathryn Johns (cjohns@.gmail.com) writes:
> Okay, I see what you're saying, but please bear with me here because I
> know I don't have the right sql mindset :-) - I'm more used to regular
> functional programming.
Then you have a bit to unlearn. :-)
> I understand the update & insert as posted by SQL, but what I don't get
> is how to determine which rows need to be inserted and which need to be
> updated *without* going through each row, one by one.
UPDATE target
SET col = s.col
FROM target t
JOIN sources s ON t.keycol = s.keycol
INSERT target (...)
SELECT ...
FROM source s
WHERE NOT EXISTS (SELECT *
FROM target t
WHERE t.keycol = s.keycol)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
I'm trying to call a stored proc from within another of my stored
procs, passing the results of a select statement as the parameters -
but it's not working. Here's what I'm trying to do:
CREATE PROCEDURE [MoveRecordsToArchive]
AS
EXEC [ARCHIVEInsert]
(
SELECT
*
FROM
[CURRENT_DATA]
)
where [ARCHIVEInsert] is a stored proc which takes parameters which are
the same as the columns in [CURRENT_DATA].
But I'm getting the following error when trying to run
MoveRecordsToArchive:
"Procedure 'MoveRecordsToArchive' expects parameter '@.ID', which was
not supplied.", where @.ID is the first parameter to [ARCHIVEInsert] and
[ID] is the first column in [CURRENT_DATA].
Am I trying to do something impossible again? If so, how else could I
go about this?You will have to do it like this
declare @.ID' int @.name varchar(50)
select @.id =id,@.name =name
from [CURRENT_DATA] where id = Somevalue
exec ARCHIVEInsert @.id ,@.name
keep in mind you can only use 1 row at a time
You can do this if you need to move more rows
INSERT INTO DATA_ARCHIVE
SELECT * FROM [CURRENT_DATA]
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Unfortunately, there may well be many rows to move, and I can't just
use your second method (i.e. a straight INSERT) since I need to insert
if the row doesn't exist in the archive table, and update if it does,
which is the process that the ARCHIVEInsert sp follows.
Is there then an easy way to iterate through all the rows in the table
so that I can use your first method?|||The insert
INSERT INTO DATA_ARCHIVE
SELECT * FROM [CURRENT_DATA] c
left join DATA_ARCHIVE d on c.id =d.id
where d.id is null
The update
update d set d.field1 = c.field1,d.field2 = c.field2,etc,etc,etc
FROM [CURRENT_DATA] c
join DATA_ARCHIVE d on c.id =d.id
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Yeah, that's not quite what I need. As I say, I have the
insert-or-update functionality working, in my ARCHIVEInsert storedproc.
I guess I could duplicate in the MoveRowsToArchive proc, but it seems
a bit silly when I have a perfectly good stored proc to call that
already does what I need.
Thanks anyway.
SQL wrote:
> The insert
> INSERT INTO DATA_ARCHIVE
> SELECT * FROM [CURRENT_DATA] c
> left join DATA_ARCHIVE d on c.id =d.id
> where d.id is null
> The update
> update d set d.field1 = c.field1,d.field2 = c.field2,etc,etc,etc
> FROM [CURRENT_DATA] c
> join DATA_ARCHIVE d on c.id =d.id
> ----
--
> "I sense many useless updates in you... Useless updates lead to
> fragmentation... Fragmentation leads to downtime...Downtime leads to
> suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
> and DBCC DBREINDEX are the force...May the force be with you" --
> http://sqlservercode.blogspot.com/|||"Cathryn Johns" <cjohns@.gmail.com> wrote in message
news:1130510060.880509.220720@.f14g2000cwb.googlegroups.com...
> Yeah, that's not quite what I need. As I say, I have the
> insert-or-update functionality working, in my ARCHIVEInsert storedproc.
> I guess I could duplicate in the MoveRowsToArchive proc, but it seems
> a bit silly when I have a perfectly good stored proc to call that
> already does what I need.
> Thanks anyway.
> SQL wrote:
>
Well, if you want to do it the hard way..
Create a CURSOR in the first stored procedure and pull one row at a time and
call your insert/update spoc in a loop.
Something like:
declare @.ID' int @.name varchar(50)
DECLARE Cur CURSOR FOR
select id, name
from [CURRENT_DATA] where id = Somevalue
OPEN Cur
FETCH NEXT FROM Cur
INTO @.ID, @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec ARCHIVEInsert @.id ,@.name
FETCH NEXT FROM Cur
INTO @.ID, @.name
END
CLOSE Cur
DEALLOCATE Cur|||Cathryn Johns (cjohns@.gmail.com) writes:
> Yeah, that's not quite what I need. As I say, I have the
> insert-or-update functionality working, in my ARCHIVEInsert storedproc.
> I guess I could duplicate in the MoveRowsToArchive proc, but it seems
> a bit silly when I have a perfectly good stored proc to call that
> already does what I need.
That's not really right. You have a stored procedure which has the
logic to do this for one single row. Now you need something that performs
the same thing for multiple rows.
You can of coruse iterate over the source table, and move one row at a
time. But, frankly, for simple logic like this, that would be about
criminal. OK, that choice of words may stun you, but if I tell you
that to move 10000 rows, it would take 50 seconds with calling the
stored procedure for each row, and five seconds with the solution
that Denis posted, you may agree. Of course, I just made those numbers
up, but the difference is really that drastic - or even worse, if
proper indexing is not in place.
RDBMS are designed to work with sets of data, and you should try to
this as much as possible.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Cathryn Johns (cjohns@.gmail.com) writes:
> That's not really right. You have a stored procedure which has the
> logic to do this for one single row. Now you need something that performs
> the same thing for multiple rows.
> You can of coruse iterate over the source table, and move one row at a
> time. But, frankly, for simple logic like this, that would be about
> criminal. OK, that choice of words may stun you, but if I tell you
> that to move 10000 rows, it would take 50 seconds with calling the
> stored procedure for each row, and five seconds with the solution
> that Denis posted, you may agree. Of course, I just made those numbers
> up, but the difference is really that drastic - or even worse, if
> proper indexing is not in place.
> RDBMS are designed to work with sets of data, and you should try to
> this as much as possible.
Okay, I see what you're saying, but please bear with me here because I
know I don't have the right sql mindset :-) - I'm more used to regular
functional programming.
I understand the update & insert as posted by SQL, but what I don't get
is how to determine which rows need to be inserted and which need to be
updated *without* going through each row, one by one. Currently my
table has a uniqueness constraint consisting of several columns, and
what my stored proc does is try to insert (since this will happen
successfully 99% of the time, I try the insert first), then checks
@.@.error and if the code indicates a uniqueness constraint violation, it
updates instead. Maybe this isn't the best way to implement what I'm
trying to accomplish, but either way I still don't see how to do this
using a set-based approach. The cursor approach makes more sense to
me, but I can see that it could be really slow.|||Cathryn,
"I understand the update & insert as posted by SQL, but what I don't
get
is how to determine which rows need to be inserted and which need to be
updated *without* going through each row, one by one"
when you do a join only rows that exist in both tables are returned so
you can do an update
when you do a left join with where d.id is null only the rows that
don't exist in the other table are returned so you can do an update
When you work with SQL you have to think in terms of sets, basically
you have to unlearn what you were taught for VB, Java C# etc etc
A very good book on T-SQL is
The Guru's Guide to Transact-SQL by Ken Henderson
http://www.amazon.com/exec/obidos/t...=glance&s=books
And of course Books On Line
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Cathryn Johns (cjohns@.gmail.com) writes:
> Okay, I see what you're saying, but please bear with me here because I
> know I don't have the right sql mindset :-) - I'm more used to regular
> functional programming.
Then you have a bit to unlearn. :-)
> I understand the update & insert as posted by SQL, but what I don't get
> is how to determine which rows need to be inserted and which need to be
> updated *without* going through each row, one by one.
UPDATE target
SET col = s.col
FROM target t
JOIN sources s ON t.keycol = s.keycol
INSERT target (...)
SELECT ...
FROM source s
WHERE NOT EXISTS (SELECT *
FROM target t
WHERE t.keycol = s.keycol)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)