Hello,
A quick question about cascading deletions...
Say I have two tables with the following data
Table A
index data
1 "some data"
2 "some other data"
Table B
index A-index data
1 2 "something"
2 1 "something else"
3 1 "out of this world"
These tables set up to perform cascading deletions when
records are removed.
My question is this: If I remove index 3 from table B,
will index 1 from table A be removed?
If not, how do you get cascading deletion to work?
Thanks
MeNo, cascade delete works "top-to-bottom", not "bottom-to-top" meaning a
foreign key with "on delete cascade", will only fire when A-index "1" is
removed from Table A. What you are seeking would cause a violation of the
foreign key because Index 2 in Table B still has reference to A-Index "2".
If you need to remove reference to all records in Table B with A-Index "1",
you need to write a trigger for the Delete event.
HTH,
Morgan
"Me" <anonymous@.discussions.microsoft.com> wrote in message
news:03f001c3c9a7$952dbd30$a101280a@.phx.gbl...
> Hello,
> A quick question about cascading deletions...
> Say I have two tables with the following data
> Table A
> index data
> 1 "some data"
> 2 "some other data"
> Table B
> index A-index data
> 1 2 "something"
> 2 1 "something else"
> 3 1 "out of this world"
>
> These tables set up to perform cascading deletions when
> records are removed.
> My question is this: If I remove index 3 from table B,
> will index 1 from table A be removed?
> If not, how do you get cascading deletion to work?
> Thanks
> Me|||Sorry this should have read..
If you need to remove the record(s) in Table A with A-Index "1" when a
record from Table B is removed, you will need to write a trigger for the
Delete event on Table B.
"Morgan" <zakirmahmood@.primposta.com> wrote in message
news:euEQE2ayDHA.2064@.TK2MSFTNGP10.phx.gbl...
> No, cascade delete works "top-to-bottom", not "bottom-to-top" meaning a
> foreign key with "on delete cascade", will only fire when A-index "1" is
> removed from Table A. What you are seeking would cause a violation of the
> foreign key because Index 2 in Table B still has reference to A-Index "2".
> If you need to remove reference to all records in Table B with A-Index
"1",
> you need to write a trigger for the Delete event.
> HTH,
> Morgan
> "Me" <anonymous@.discussions.microsoft.com> wrote in message
> news:03f001c3c9a7$952dbd30$a101280a@.phx.gbl...
> > Hello,
> >
> > A quick question about cascading deletions...
> >
> > Say I have two tables with the following data
> >
> > Table A
> > index data
> > 1 "some data"
> > 2 "some other data"
> >
> > Table B
> > index A-index data
> > 1 2 "something"
> > 2 1 "something else"
> > 3 1 "out of this world"
> >
> >
> > These tables set up to perform cascading deletions when
> > records are removed.
> >
> > My question is this: If I remove index 3 from table B,
> > will index 1 from table A be removed?
> >
> > If not, how do you get cascading deletion to work?
> >
> > Thanks
> > Me
>|||Thanks for the info !!
>--Original Message--
>Sorry this should have read..
> If you need to remove the record(s) in Table A with A-
Index "1" when a
>record from Table B is removed, you will need to write a
trigger for the
>Delete event on Table B.
>"Morgan" <zakirmahmood@.primposta.com> wrote in message
>news:euEQE2ayDHA.2064@.TK2MSFTNGP10.phx.gbl...
>> No, cascade delete works "top-to-bottom", not "bottom-
to-top" meaning a
>> foreign key with "on delete cascade", will only fire
when A-index "1" is
>> removed from Table A. What you are seeking would cause
a violation of the
>> foreign key because Index 2 in Table B still has
reference to A-Index "2".
>> If you need to remove reference to all records in Table
B with A-Index
>"1",
>> you need to write a trigger for the Delete event.
>> HTH,
>> Morgan
>> "Me" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:03f001c3c9a7$952dbd30$a101280a@.phx.gbl...
>> > Hello,
>> >
>> > A quick question about cascading deletions...
>> >
>> > Say I have two tables with the following data
>> >
>> > Table A
>> > index data
>> > 1 "some data"
>> > 2 "some other data"
>> >
>> > Table B
>> > index A-index data
>> > 1 2 "something"
>> > 2 1 "something else"
>> > 3 1 "out of this world"
>> >
>> >
>> > These tables set up to perform cascading deletions
when
>> > records are removed.
>> >
>> > My question is this: If I remove index 3 from table
B,
>> > will index 1 from table A be removed?
>> >
>> > If not, how do you get cascading deletion to work?
>> >
>> > Thanks
>> > Me
>>
>
>.
>
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Sunday, March 11, 2012
Friday, February 24, 2012
Capture table & index scanning
I have a table I believe is the subject of excessive
scanning on the table & its indexes. I am having trouble
getting SQLProfiler to capture the calls to SPs that are
causing scans on the table. Anybody know how to config
SQLProfiler to do this or a better way?
Thanks,
RogerHave you set filters to search for the SP name?
TextData--> Like %whatever%
I use this combined with the databaseid filter to limit the thousands of
rows my dbs put out to Profiler
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1f72301c457be$1c6e9b30$a401280a@.phx
.gbl...
> I have a table I believe is the subject of excessive
> scanning on the table & its indexes. I am having trouble
> getting SQLProfiler to capture the calls to SPs that are
> causing scans on the table. Anybody know how to config
> SQLProfiler to do this or a better way?
> Thanks,
> Roger|||Roger
http://www.sql-server-performance.c...ofiler_tips.asp
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1f72301c457be$1c6e9b30$a401280a@.phx
.gbl...
> I have a table I believe is the subject of excessive
> scanning on the table & its indexes. I am having trouble
> getting SQLProfiler to capture the calls to SPs that are
> causing scans on the table. Anybody know how to config
> SQLProfiler to do this or a better way?
> Thanks,
> Roger|||Thanks for the reply Kevin. I know the table & its
actually the SPs I'm trying to identify. Here's my
Profiler settings:
Events:
Scan:Started
RPC:Completed
SP:Completed
Data Columns:
IndexID (Grouped)
EventClass
StartTime
SPID
Duration
TextData
HostName
DatabaseID
ApplicationName
CPU
Reads
Filters:
DatabaseID = 8
ObjectID = 295776211 (the table thats being scanned)
I suspect the table is being scanned because SQL slows
down, CPU goes up & I run sp_lock & a few SPIDs have
taken out 2million+ locks on the table. The above
Profiler settings indicate scans, but doesn't tell me
what SPs are running when the scan occurs. Any help
appreciated.
Thanks,
Roger
>--Original Message--
>Have you set filters to search for the SP name?
>TextData--> Like %whatever%
>I use this combined with the databaseid filter to limit
the thousands of
>rows my dbs put out to Profiler
>--
>Kevin Hill
>President
>3NF Consulting
>www.3nf-inc.com/NewsGroups.htm
>www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
Worth area DBAs.
>"Roger White" <anonymous@.discussions.microsoft.com>
wrote in message
> news:1f72301c457be$1c6e9b30$a401280a@.phx
.gbl...
trouble[vbcol=seagreen]
are[vbcol=seagreen]
>
>.
>|||Thanks Uri. I had already found this article. The whole
site is excellent. Please see my reply to Kevin. I
would appreciate any more help as this is really hurting
our production system.
Thanks,
Roger
>--Original Message--
>Roger
>http://www.sql-server-
performance.com/sql_server_profiler_tips.asp
>|||Roger
Can you identify where does the table involve?
Look at these stored procedure/queries.
Have you appropriate indexes for searching on the table?
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fa4c01c45836$aad376c0$a501280a@.phx
.gbl...[vbcol=seagreen]
> Thanks Uri. I had already found this article. The whole
> site is excellent. Please see my reply to Kevin. I
> would appreciate any more help as this is really hurting
> our production system.
> Thanks,
> Roger
> performance.com/sql_server_profiler_tips.asp|||Uri,
We have 4000 SPs. The table is probably called by
hundreds of these SPs. Through knowledge of the front-
ends, we have clustered, non-clustered & composite
indexes which we have seen improvements in some SPs, but
we are still experiencing problems. Can I assume that
SQLProfiler can't do this?
Thanks,
Roger
>--Original Message--
>Roger
>Can you identify where does the table involve?
>Look at these stored procedure/queries.
>Have you appropriate indexes for searching on the table?
>
>
>"Roger White" <anonymous@.discussions.microsoft.com>
wrote in message
> news:1fa4c01c45836$aad376c0$a501280a@.phx
.gbl...
whole[vbcol=seagreen]
hurting[vbcol=seagreen]
>
>.
>|||Can you set a filter on the duration to only show those that are taking over
a certain amount of time?
I have a trace template established to identify anything over 5000 (5
seconds) in my environment...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fddf01c45835$fc33ca20$a001280a@.phx
.gbl...[vbcol=seagreen]
> Thanks for the reply Kevin. I know the table & its
> actually the SPs I'm trying to identify. Here's my
> Profiler settings:
> Events:
> Scan:Started
> RPC:Completed
> SP:Completed
> Data Columns:
> IndexID (Grouped)
> EventClass
> StartTime
> SPID
> Duration
> TextData
> HostName
> DatabaseID
> ApplicationName
> CPU
> Reads
> Filters:
> DatabaseID = 8
> ObjectID = 295776211 (the table thats being scanned)
> I suspect the table is being scanned because SQL slows
> down, CPU goes up & I run sp_lock & a few SPIDs have
> taken out 2million+ locks on the table. The above
> Profiler settings indicate scans, but doesn't tell me
> what SPs are running when the scan occurs. Any help
> appreciated.
> Thanks,
> Roger
>
> the thousands of
> Worth area DBAs.
> wrote in message
> trouble
> are|||Roger
Well, I would go with idetifying those stored procedures (although it might
be >100) and then run SQL Server Profiler to capture a long running stored
procedures (group by duration).
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fa4401c4584c$6739b350$a101280a@.phx
.gbl...[vbcol=seagreen]
> Uri,
> We have 4000 SPs. The table is probably called by
> hundreds of these SPs. Through knowledge of the front-
> ends, we have clustered, non-clustered & composite
> indexes which we have seen improvements in some SPs, but
> we are still experiencing problems. Can I assume that
> SQLProfiler can't do this?
> Thanks,
> Roger
>
> wrote in message
> whole
> hurting
scanning on the table & its indexes. I am having trouble
getting SQLProfiler to capture the calls to SPs that are
causing scans on the table. Anybody know how to config
SQLProfiler to do this or a better way?
Thanks,
RogerHave you set filters to search for the SP name?
TextData--> Like %whatever%
I use this combined with the databaseid filter to limit the thousands of
rows my dbs put out to Profiler
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1f72301c457be$1c6e9b30$a401280a@.phx
.gbl...
> I have a table I believe is the subject of excessive
> scanning on the table & its indexes. I am having trouble
> getting SQLProfiler to capture the calls to SPs that are
> causing scans on the table. Anybody know how to config
> SQLProfiler to do this or a better way?
> Thanks,
> Roger|||Roger
http://www.sql-server-performance.c...ofiler_tips.asp
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1f72301c457be$1c6e9b30$a401280a@.phx
.gbl...
> I have a table I believe is the subject of excessive
> scanning on the table & its indexes. I am having trouble
> getting SQLProfiler to capture the calls to SPs that are
> causing scans on the table. Anybody know how to config
> SQLProfiler to do this or a better way?
> Thanks,
> Roger|||Thanks for the reply Kevin. I know the table & its
actually the SPs I'm trying to identify. Here's my
Profiler settings:
Events:
Scan:Started
RPC:Completed
SP:Completed
Data Columns:
IndexID (Grouped)
EventClass
StartTime
SPID
Duration
TextData
HostName
DatabaseID
ApplicationName
CPU
Reads
Filters:
DatabaseID = 8
ObjectID = 295776211 (the table thats being scanned)
I suspect the table is being scanned because SQL slows
down, CPU goes up & I run sp_lock & a few SPIDs have
taken out 2million+ locks on the table. The above
Profiler settings indicate scans, but doesn't tell me
what SPs are running when the scan occurs. Any help
appreciated.
Thanks,
Roger
>--Original Message--
>Have you set filters to search for the SP name?
>TextData--> Like %whatever%
>I use this combined with the databaseid filter to limit
the thousands of
>rows my dbs put out to Profiler
>--
>Kevin Hill
>President
>3NF Consulting
>www.3nf-inc.com/NewsGroups.htm
>www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
Worth area DBAs.
>"Roger White" <anonymous@.discussions.microsoft.com>
wrote in message
> news:1f72301c457be$1c6e9b30$a401280a@.phx
.gbl...
trouble[vbcol=seagreen]
are[vbcol=seagreen]
>
>.
>|||Thanks Uri. I had already found this article. The whole
site is excellent. Please see my reply to Kevin. I
would appreciate any more help as this is really hurting
our production system.
Thanks,
Roger
>--Original Message--
>Roger
>http://www.sql-server-
performance.com/sql_server_profiler_tips.asp
>|||Roger
Can you identify where does the table involve?
Look at these stored procedure/queries.
Have you appropriate indexes for searching on the table?
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fa4c01c45836$aad376c0$a501280a@.phx
.gbl...[vbcol=seagreen]
> Thanks Uri. I had already found this article. The whole
> site is excellent. Please see my reply to Kevin. I
> would appreciate any more help as this is really hurting
> our production system.
> Thanks,
> Roger
> performance.com/sql_server_profiler_tips.asp|||Uri,
We have 4000 SPs. The table is probably called by
hundreds of these SPs. Through knowledge of the front-
ends, we have clustered, non-clustered & composite
indexes which we have seen improvements in some SPs, but
we are still experiencing problems. Can I assume that
SQLProfiler can't do this?
Thanks,
Roger
>--Original Message--
>Roger
>Can you identify where does the table involve?
>Look at these stored procedure/queries.
>Have you appropriate indexes for searching on the table?
>
>
>"Roger White" <anonymous@.discussions.microsoft.com>
wrote in message
> news:1fa4c01c45836$aad376c0$a501280a@.phx
.gbl...
whole[vbcol=seagreen]
hurting[vbcol=seagreen]
>
>.
>|||Can you set a filter on the duration to only show those that are taking over
a certain amount of time?
I have a trace template established to identify anything over 5000 (5
seconds) in my environment...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fddf01c45835$fc33ca20$a001280a@.phx
.gbl...[vbcol=seagreen]
> Thanks for the reply Kevin. I know the table & its
> actually the SPs I'm trying to identify. Here's my
> Profiler settings:
> Events:
> Scan:Started
> RPC:Completed
> SP:Completed
> Data Columns:
> IndexID (Grouped)
> EventClass
> StartTime
> SPID
> Duration
> TextData
> HostName
> DatabaseID
> ApplicationName
> CPU
> Reads
> Filters:
> DatabaseID = 8
> ObjectID = 295776211 (the table thats being scanned)
> I suspect the table is being scanned because SQL slows
> down, CPU goes up & I run sp_lock & a few SPIDs have
> taken out 2million+ locks on the table. The above
> Profiler settings indicate scans, but doesn't tell me
> what SPs are running when the scan occurs. Any help
> appreciated.
> Thanks,
> Roger
>
> the thousands of
> Worth area DBAs.
> wrote in message
> trouble
> are|||Roger
Well, I would go with idetifying those stored procedures (although it might
be >100) and then run SQL Server Profiler to capture a long running stored
procedures (group by duration).
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fa4401c4584c$6739b350$a101280a@.phx
.gbl...[vbcol=seagreen]
> Uri,
> We have 4000 SPs. The table is probably called by
> hundreds of these SPs. Through knowledge of the front-
> ends, we have clustered, non-clustered & composite
> indexes which we have seen improvements in some SPs, but
> we are still experiencing problems. Can I assume that
> SQLProfiler can't do this?
> Thanks,
> Roger
>
> wrote in message
> whole
> hurting
Capture table & index scanning
I have a table I believe is the subject of excessive
scanning on the table & its indexes. I am having trouble
getting SQLProfiler to capture the calls to SPs that are
causing scans on the table. Anybody know how to config
SQLProfiler to do this or a better way?
Thanks,
RogerHave you set filters to search for the SP name?
TextData--> Like %whatever%
I use this combined with the databaseid filter to limit the thousands of
rows my dbs put out to Profiler
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1f72301c457be$1c6e9b30$a401280a@.phx.gbl...
> I have a table I believe is the subject of excessive
> scanning on the table & its indexes. I am having trouble
> getting SQLProfiler to capture the calls to SPs that are
> causing scans on the table. Anybody know how to config
> SQLProfiler to do this or a better way?
> Thanks,
> Roger|||Roger
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1f72301c457be$1c6e9b30$a401280a@.phx.gbl...
> I have a table I believe is the subject of excessive
> scanning on the table & its indexes. I am having trouble
> getting SQLProfiler to capture the calls to SPs that are
> causing scans on the table. Anybody know how to config
> SQLProfiler to do this or a better way?
> Thanks,
> Roger|||Thanks for the reply Kevin. I know the table & its
actually the SPs I'm trying to identify. Here's my
Profiler settings:
Events:
Scan:Started
RPC:Completed
SP:Completed
Data Columns:
IndexID (Grouped)
EventClass
StartTime
SPID
Duration
TextData
HostName
DatabaseID
ApplicationName
CPU
Reads
Filters:
DatabaseID = 8
ObjectID = 295776211 (the table thats being scanned)
I suspect the table is being scanned because SQL slows
down, CPU goes up & I run sp_lock & a few SPIDs have
taken out 2million+ locks on the table. The above
Profiler settings indicate scans, but doesn't tell me
what SPs are running when the scan occurs. Any help
appreciated.
Thanks,
Roger
>--Original Message--
>Have you set filters to search for the SP name?
>TextData--> Like %whatever%
>I use this combined with the databaseid filter to limit
the thousands of
>rows my dbs put out to Profiler
>--
>Kevin Hill
>President
>3NF Consulting
>www.3nf-inc.com/NewsGroups.htm
>www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
Worth area DBAs.
>"Roger White" <anonymous@.discussions.microsoft.com>
wrote in message
>news:1f72301c457be$1c6e9b30$a401280a@.phx.gbl...
>> I have a table I believe is the subject of excessive
>> scanning on the table & its indexes. I am having
trouble
>> getting SQLProfiler to capture the calls to SPs that
are
>> causing scans on the table. Anybody know how to config
>> SQLProfiler to do this or a better way?
>> Thanks,
>> Roger
>
>.
>|||Thanks Uri. I had already found this article. The whole
site is excellent. Please see my reply to Kevin. I
would appreciate any more help as this is really hurting
our production system.
Thanks,
Roger
>--Original Message--
>Roger
>http://www.sql-server-
performance.com/sql_server_profiler_tips.asp
>|||Roger
Can you identify where does the table involve?
Look at these stored procedure/queries.
Have you appropriate indexes for searching on the table?
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fa4c01c45836$aad376c0$a501280a@.phx.gbl...
> Thanks Uri. I had already found this article. The whole
> site is excellent. Please see my reply to Kevin. I
> would appreciate any more help as this is really hurting
> our production system.
> Thanks,
> Roger
> >--Original Message--
> >Roger
> >http://www.sql-server-
> performance.com/sql_server_profiler_tips.asp
> >|||Uri,
We have 4000 SPs. The table is probably called by
hundreds of these SPs. Through knowledge of the front-
ends, we have clustered, non-clustered & composite
indexes which we have seen improvements in some SPs, but
we are still experiencing problems. Can I assume that
SQLProfiler can't do this?
Thanks,
Roger
>--Original Message--
>Roger
>Can you identify where does the table involve?
>Look at these stored procedure/queries.
>Have you appropriate indexes for searching on the table?
>
>
>"Roger White" <anonymous@.discussions.microsoft.com>
wrote in message
>news:1fa4c01c45836$aad376c0$a501280a@.phx.gbl...
>> Thanks Uri. I had already found this article. The
whole
>> site is excellent. Please see my reply to Kevin. I
>> would appreciate any more help as this is really
hurting
>> our production system.
>> Thanks,
>> Roger
>> >--Original Message--
>> >Roger
>> >http://www.sql-server-
>> performance.com/sql_server_profiler_tips.asp
>> >
>
>.
>|||Can you set a filter on the duration to only show those that are taking over
a certain amount of time?
I have a trace template established to identify anything over 5000 (5
seconds) in my environment...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fddf01c45835$fc33ca20$a001280a@.phx.gbl...
> Thanks for the reply Kevin. I know the table & its
> actually the SPs I'm trying to identify. Here's my
> Profiler settings:
> Events:
> Scan:Started
> RPC:Completed
> SP:Completed
> Data Columns:
> IndexID (Grouped)
> EventClass
> StartTime
> SPID
> Duration
> TextData
> HostName
> DatabaseID
> ApplicationName
> CPU
> Reads
> Filters:
> DatabaseID = 8
> ObjectID = 295776211 (the table thats being scanned)
> I suspect the table is being scanned because SQL slows
> down, CPU goes up & I run sp_lock & a few SPIDs have
> taken out 2million+ locks on the table. The above
> Profiler settings indicate scans, but doesn't tell me
> what SPs are running when the scan occurs. Any help
> appreciated.
> Thanks,
> Roger
> >--Original Message--
> >Have you set filters to search for the SP name?
> >
> >TextData--> Like %whatever%
> >
> >I use this combined with the databaseid filter to limit
> the thousands of
> >rows my dbs put out to Profiler
> >
> >--
> >Kevin Hill
> >President
> >3NF Consulting
> >
> >www.3nf-inc.com/NewsGroups.htm
> >www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
> Worth area DBAs.
> >
> >"Roger White" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:1f72301c457be$1c6e9b30$a401280a@.phx.gbl...
> >> I have a table I believe is the subject of excessive
> >> scanning on the table & its indexes. I am having
> trouble
> >> getting SQLProfiler to capture the calls to SPs that
> are
> >> causing scans on the table. Anybody know how to config
> >> SQLProfiler to do this or a better way?
> >>
> >> Thanks,
> >> Roger
> >
> >
> >.
> >|||Roger
Well, I would go with idetifying those stored procedures (although it might
be >100) and then run SQL Server Profiler to capture a long running stored
procedures (group by duration).
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fa4401c4584c$6739b350$a101280a@.phx.gbl...
> Uri,
> We have 4000 SPs. The table is probably called by
> hundreds of these SPs. Through knowledge of the front-
> ends, we have clustered, non-clustered & composite
> indexes which we have seen improvements in some SPs, but
> we are still experiencing problems. Can I assume that
> SQLProfiler can't do this?
> Thanks,
> Roger
> >--Original Message--
> >Roger
> >Can you identify where does the table involve?
> >Look at these stored procedure/queries.
> >Have you appropriate indexes for searching on the table?
> >
> >
> >
> >
> >"Roger White" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:1fa4c01c45836$aad376c0$a501280a@.phx.gbl...
> >> Thanks Uri. I had already found this article. The
> whole
> >> site is excellent. Please see my reply to Kevin. I
> >> would appreciate any more help as this is really
> hurting
> >> our production system.
> >>
> >> Thanks,
> >>
> >> Roger
> >> >--Original Message--
> >> >Roger
> >> >http://www.sql-server-
> >> performance.com/sql_server_profiler_tips.asp
> >> >
> >
> >
> >.
> >
scanning on the table & its indexes. I am having trouble
getting SQLProfiler to capture the calls to SPs that are
causing scans on the table. Anybody know how to config
SQLProfiler to do this or a better way?
Thanks,
RogerHave you set filters to search for the SP name?
TextData--> Like %whatever%
I use this combined with the databaseid filter to limit the thousands of
rows my dbs put out to Profiler
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1f72301c457be$1c6e9b30$a401280a@.phx.gbl...
> I have a table I believe is the subject of excessive
> scanning on the table & its indexes. I am having trouble
> getting SQLProfiler to capture the calls to SPs that are
> causing scans on the table. Anybody know how to config
> SQLProfiler to do this or a better way?
> Thanks,
> Roger|||Roger
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1f72301c457be$1c6e9b30$a401280a@.phx.gbl...
> I have a table I believe is the subject of excessive
> scanning on the table & its indexes. I am having trouble
> getting SQLProfiler to capture the calls to SPs that are
> causing scans on the table. Anybody know how to config
> SQLProfiler to do this or a better way?
> Thanks,
> Roger|||Thanks for the reply Kevin. I know the table & its
actually the SPs I'm trying to identify. Here's my
Profiler settings:
Events:
Scan:Started
RPC:Completed
SP:Completed
Data Columns:
IndexID (Grouped)
EventClass
StartTime
SPID
Duration
TextData
HostName
DatabaseID
ApplicationName
CPU
Reads
Filters:
DatabaseID = 8
ObjectID = 295776211 (the table thats being scanned)
I suspect the table is being scanned because SQL slows
down, CPU goes up & I run sp_lock & a few SPIDs have
taken out 2million+ locks on the table. The above
Profiler settings indicate scans, but doesn't tell me
what SPs are running when the scan occurs. Any help
appreciated.
Thanks,
Roger
>--Original Message--
>Have you set filters to search for the SP name?
>TextData--> Like %whatever%
>I use this combined with the databaseid filter to limit
the thousands of
>rows my dbs put out to Profiler
>--
>Kevin Hill
>President
>3NF Consulting
>www.3nf-inc.com/NewsGroups.htm
>www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
Worth area DBAs.
>"Roger White" <anonymous@.discussions.microsoft.com>
wrote in message
>news:1f72301c457be$1c6e9b30$a401280a@.phx.gbl...
>> I have a table I believe is the subject of excessive
>> scanning on the table & its indexes. I am having
trouble
>> getting SQLProfiler to capture the calls to SPs that
are
>> causing scans on the table. Anybody know how to config
>> SQLProfiler to do this or a better way?
>> Thanks,
>> Roger
>
>.
>|||Thanks Uri. I had already found this article. The whole
site is excellent. Please see my reply to Kevin. I
would appreciate any more help as this is really hurting
our production system.
Thanks,
Roger
>--Original Message--
>Roger
>http://www.sql-server-
performance.com/sql_server_profiler_tips.asp
>|||Roger
Can you identify where does the table involve?
Look at these stored procedure/queries.
Have you appropriate indexes for searching on the table?
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fa4c01c45836$aad376c0$a501280a@.phx.gbl...
> Thanks Uri. I had already found this article. The whole
> site is excellent. Please see my reply to Kevin. I
> would appreciate any more help as this is really hurting
> our production system.
> Thanks,
> Roger
> >--Original Message--
> >Roger
> >http://www.sql-server-
> performance.com/sql_server_profiler_tips.asp
> >|||Uri,
We have 4000 SPs. The table is probably called by
hundreds of these SPs. Through knowledge of the front-
ends, we have clustered, non-clustered & composite
indexes which we have seen improvements in some SPs, but
we are still experiencing problems. Can I assume that
SQLProfiler can't do this?
Thanks,
Roger
>--Original Message--
>Roger
>Can you identify where does the table involve?
>Look at these stored procedure/queries.
>Have you appropriate indexes for searching on the table?
>
>
>"Roger White" <anonymous@.discussions.microsoft.com>
wrote in message
>news:1fa4c01c45836$aad376c0$a501280a@.phx.gbl...
>> Thanks Uri. I had already found this article. The
whole
>> site is excellent. Please see my reply to Kevin. I
>> would appreciate any more help as this is really
hurting
>> our production system.
>> Thanks,
>> Roger
>> >--Original Message--
>> >Roger
>> >http://www.sql-server-
>> performance.com/sql_server_profiler_tips.asp
>> >
>
>.
>|||Can you set a filter on the duration to only show those that are taking over
a certain amount of time?
I have a trace template established to identify anything over 5000 (5
seconds) in my environment...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fddf01c45835$fc33ca20$a001280a@.phx.gbl...
> Thanks for the reply Kevin. I know the table & its
> actually the SPs I'm trying to identify. Here's my
> Profiler settings:
> Events:
> Scan:Started
> RPC:Completed
> SP:Completed
> Data Columns:
> IndexID (Grouped)
> EventClass
> StartTime
> SPID
> Duration
> TextData
> HostName
> DatabaseID
> ApplicationName
> CPU
> Reads
> Filters:
> DatabaseID = 8
> ObjectID = 295776211 (the table thats being scanned)
> I suspect the table is being scanned because SQL slows
> down, CPU goes up & I run sp_lock & a few SPIDs have
> taken out 2million+ locks on the table. The above
> Profiler settings indicate scans, but doesn't tell me
> what SPs are running when the scan occurs. Any help
> appreciated.
> Thanks,
> Roger
> >--Original Message--
> >Have you set filters to search for the SP name?
> >
> >TextData--> Like %whatever%
> >
> >I use this combined with the databaseid filter to limit
> the thousands of
> >rows my dbs put out to Profiler
> >
> >--
> >Kevin Hill
> >President
> >3NF Consulting
> >
> >www.3nf-inc.com/NewsGroups.htm
> >www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
> Worth area DBAs.
> >
> >"Roger White" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:1f72301c457be$1c6e9b30$a401280a@.phx.gbl...
> >> I have a table I believe is the subject of excessive
> >> scanning on the table & its indexes. I am having
> trouble
> >> getting SQLProfiler to capture the calls to SPs that
> are
> >> causing scans on the table. Anybody know how to config
> >> SQLProfiler to do this or a better way?
> >>
> >> Thanks,
> >> Roger
> >
> >
> >.
> >|||Roger
Well, I would go with idetifying those stored procedures (although it might
be >100) and then run SQL Server Profiler to capture a long running stored
procedures (group by duration).
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fa4401c4584c$6739b350$a101280a@.phx.gbl...
> Uri,
> We have 4000 SPs. The table is probably called by
> hundreds of these SPs. Through knowledge of the front-
> ends, we have clustered, non-clustered & composite
> indexes which we have seen improvements in some SPs, but
> we are still experiencing problems. Can I assume that
> SQLProfiler can't do this?
> Thanks,
> Roger
> >--Original Message--
> >Roger
> >Can you identify where does the table involve?
> >Look at these stored procedure/queries.
> >Have you appropriate indexes for searching on the table?
> >
> >
> >
> >
> >"Roger White" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:1fa4c01c45836$aad376c0$a501280a@.phx.gbl...
> >> Thanks Uri. I had already found this article. The
> whole
> >> site is excellent. Please see my reply to Kevin. I
> >> would appreciate any more help as this is really
> hurting
> >> our production system.
> >>
> >> Thanks,
> >>
> >> Roger
> >> >--Original Message--
> >> >Roger
> >> >http://www.sql-server-
> >> performance.com/sql_server_profiler_tips.asp
> >> >
> >
> >
> >.
> >
Capture table & index scanning
I have a table I believe is the subject of excessive
scanning on the table & its indexes. I am having trouble
getting SQLProfiler to capture the calls to SPs that are
causing scans on the table. Anybody know how to config
SQLProfiler to do this or a better way?
Thanks,
Roger
Have you set filters to search for the SP name?
TextData--> Like %whatever%
I use this combined with the databaseid filter to limit the thousands of
rows my dbs put out to Profiler
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1f72301c457be$1c6e9b30$a401280a@.phx.gbl...
> I have a table I believe is the subject of excessive
> scanning on the table & its indexes. I am having trouble
> getting SQLProfiler to capture the calls to SPs that are
> causing scans on the table. Anybody know how to config
> SQLProfiler to do this or a better way?
> Thanks,
> Roger
|||Roger
http://www.sql-server-performance.co...filer_tips.asp
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1f72301c457be$1c6e9b30$a401280a@.phx.gbl...
> I have a table I believe is the subject of excessive
> scanning on the table & its indexes. I am having trouble
> getting SQLProfiler to capture the calls to SPs that are
> causing scans on the table. Anybody know how to config
> SQLProfiler to do this or a better way?
> Thanks,
> Roger
|||Thanks for the reply Kevin. I know the table & its
actually the SPs I'm trying to identify. Here's my
Profiler settings:
Events:
Scan:Started
RPC:Completed
SP:Completed
Data Columns:
IndexID (Grouped)
EventClass
StartTime
SPID
Duration
TextData
HostName
DatabaseID
ApplicationName
CPU
Reads
Filters:
DatabaseID = 8
ObjectID = 295776211 (the table thats being scanned)
I suspect the table is being scanned because SQL slows
down, CPU goes up & I run sp_lock & a few SPIDs have
taken out 2million+ locks on the table. The above
Profiler settings indicate scans, but doesn't tell me
what SPs are running when the scan occurs. Any help
appreciated.
Thanks,
Roger
>--Original Message--
>Have you set filters to search for the SP name?
>TextData--> Like %whatever%
>I use this combined with the databaseid filter to limit
the thousands of
>rows my dbs put out to Profiler
>--
>Kevin Hill
>President
>3NF Consulting
>www.3nf-inc.com/NewsGroups.htm
>www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
Worth area DBAs.
>"Roger White" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:1f72301c457be$1c6e9b30$a401280a@.phx.gbl...
trouble[vbcol=seagreen]
are
>
>.
>
|||Thanks Uri. I had already found this article. The whole
site is excellent. Please see my reply to Kevin. I
would appreciate any more help as this is really hurting
our production system.
Thanks,
Roger
>--Original Message--
>Roger
>http://www.sql-server-
performance.com/sql_server_profiler_tips.asp
>
|||Roger
Can you identify where does the table involve?
Look at these stored procedure/queries.
Have you appropriate indexes for searching on the table?
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fa4c01c45836$aad376c0$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks Uri. I had already found this article. The whole
> site is excellent. Please see my reply to Kevin. I
> would appreciate any more help as this is really hurting
> our production system.
> Thanks,
> Roger
> performance.com/sql_server_profiler_tips.asp
|||Uri,
We have 4000 SPs. The table is probably called by
hundreds of these SPs. Through knowledge of the front-
ends, we have clustered, non-clustered & composite
indexes which we have seen improvements in some SPs, but
we are still experiencing problems. Can I assume that
SQLProfiler can't do this?
Thanks,
Roger
>--Original Message--
>Roger
>Can you identify where does the table involve?
>Look at these stored procedure/queries.
>Have you appropriate indexes for searching on the table?
>
>
>"Roger White" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:1fa4c01c45836$aad376c0$a501280a@.phx.gbl...
whole[vbcol=seagreen]
hurting
>
>.
>
|||Can you set a filter on the duration to only show those that are taking over
a certain amount of time?
I have a trace template established to identify anything over 5000 (5
seconds) in my environment...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fddf01c45835$fc33ca20$a001280a@.phx.gbl...[vbcol=seagreen]
> Thanks for the reply Kevin. I know the table & its
> actually the SPs I'm trying to identify. Here's my
> Profiler settings:
> Events:
> Scan:Started
> RPC:Completed
> SP:Completed
> Data Columns:
> IndexID (Grouped)
> EventClass
> StartTime
> SPID
> Duration
> TextData
> HostName
> DatabaseID
> ApplicationName
> CPU
> Reads
> Filters:
> DatabaseID = 8
> ObjectID = 295776211 (the table thats being scanned)
> I suspect the table is being scanned because SQL slows
> down, CPU goes up & I run sp_lock & a few SPIDs have
> taken out 2million+ locks on the table. The above
> Profiler settings indicate scans, but doesn't tell me
> what SPs are running when the scan occurs. Any help
> appreciated.
> Thanks,
> Roger
> the thousands of
> Worth area DBAs.
> wrote in message
> trouble
> are
|||Roger
Well, I would go with idetifying those stored procedures (although it might
be >100) and then run SQL Server Profiler to capture a long running stored
procedures (group by duration).
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fa4401c4584c$6739b350$a101280a@.phx.gbl...[vbcol=seagreen]
> Uri,
> We have 4000 SPs. The table is probably called by
> hundreds of these SPs. Through knowledge of the front-
> ends, we have clustered, non-clustered & composite
> indexes which we have seen improvements in some SPs, but
> we are still experiencing problems. Can I assume that
> SQLProfiler can't do this?
> Thanks,
> Roger
> wrote in message
> whole
> hurting
scanning on the table & its indexes. I am having trouble
getting SQLProfiler to capture the calls to SPs that are
causing scans on the table. Anybody know how to config
SQLProfiler to do this or a better way?
Thanks,
Roger
Have you set filters to search for the SP name?
TextData--> Like %whatever%
I use this combined with the databaseid filter to limit the thousands of
rows my dbs put out to Profiler
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1f72301c457be$1c6e9b30$a401280a@.phx.gbl...
> I have a table I believe is the subject of excessive
> scanning on the table & its indexes. I am having trouble
> getting SQLProfiler to capture the calls to SPs that are
> causing scans on the table. Anybody know how to config
> SQLProfiler to do this or a better way?
> Thanks,
> Roger
|||Roger
http://www.sql-server-performance.co...filer_tips.asp
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1f72301c457be$1c6e9b30$a401280a@.phx.gbl...
> I have a table I believe is the subject of excessive
> scanning on the table & its indexes. I am having trouble
> getting SQLProfiler to capture the calls to SPs that are
> causing scans on the table. Anybody know how to config
> SQLProfiler to do this or a better way?
> Thanks,
> Roger
|||Thanks for the reply Kevin. I know the table & its
actually the SPs I'm trying to identify. Here's my
Profiler settings:
Events:
Scan:Started
RPC:Completed
SP:Completed
Data Columns:
IndexID (Grouped)
EventClass
StartTime
SPID
Duration
TextData
HostName
DatabaseID
ApplicationName
CPU
Reads
Filters:
DatabaseID = 8
ObjectID = 295776211 (the table thats being scanned)
I suspect the table is being scanned because SQL slows
down, CPU goes up & I run sp_lock & a few SPIDs have
taken out 2million+ locks on the table. The above
Profiler settings indicate scans, but doesn't tell me
what SPs are running when the scan occurs. Any help
appreciated.
Thanks,
Roger
>--Original Message--
>Have you set filters to search for the SP name?
>TextData--> Like %whatever%
>I use this combined with the databaseid filter to limit
the thousands of
>rows my dbs put out to Profiler
>--
>Kevin Hill
>President
>3NF Consulting
>www.3nf-inc.com/NewsGroups.htm
>www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
Worth area DBAs.
>"Roger White" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:1f72301c457be$1c6e9b30$a401280a@.phx.gbl...
trouble[vbcol=seagreen]
are
>
>.
>
|||Thanks Uri. I had already found this article. The whole
site is excellent. Please see my reply to Kevin. I
would appreciate any more help as this is really hurting
our production system.
Thanks,
Roger
>--Original Message--
>Roger
>http://www.sql-server-
performance.com/sql_server_profiler_tips.asp
>
|||Roger
Can you identify where does the table involve?
Look at these stored procedure/queries.
Have you appropriate indexes for searching on the table?
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fa4c01c45836$aad376c0$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks Uri. I had already found this article. The whole
> site is excellent. Please see my reply to Kevin. I
> would appreciate any more help as this is really hurting
> our production system.
> Thanks,
> Roger
> performance.com/sql_server_profiler_tips.asp
|||Uri,
We have 4000 SPs. The table is probably called by
hundreds of these SPs. Through knowledge of the front-
ends, we have clustered, non-clustered & composite
indexes which we have seen improvements in some SPs, but
we are still experiencing problems. Can I assume that
SQLProfiler can't do this?
Thanks,
Roger
>--Original Message--
>Roger
>Can you identify where does the table involve?
>Look at these stored procedure/queries.
>Have you appropriate indexes for searching on the table?
>
>
>"Roger White" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:1fa4c01c45836$aad376c0$a501280a@.phx.gbl...
whole[vbcol=seagreen]
hurting
>
>.
>
|||Can you set a filter on the duration to only show those that are taking over
a certain amount of time?
I have a trace template established to identify anything over 5000 (5
seconds) in my environment...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fddf01c45835$fc33ca20$a001280a@.phx.gbl...[vbcol=seagreen]
> Thanks for the reply Kevin. I know the table & its
> actually the SPs I'm trying to identify. Here's my
> Profiler settings:
> Events:
> Scan:Started
> RPC:Completed
> SP:Completed
> Data Columns:
> IndexID (Grouped)
> EventClass
> StartTime
> SPID
> Duration
> TextData
> HostName
> DatabaseID
> ApplicationName
> CPU
> Reads
> Filters:
> DatabaseID = 8
> ObjectID = 295776211 (the table thats being scanned)
> I suspect the table is being scanned because SQL slows
> down, CPU goes up & I run sp_lock & a few SPIDs have
> taken out 2million+ locks on the table. The above
> Profiler settings indicate scans, but doesn't tell me
> what SPs are running when the scan occurs. Any help
> appreciated.
> Thanks,
> Roger
> the thousands of
> Worth area DBAs.
> wrote in message
> trouble
> are
|||Roger
Well, I would go with idetifying those stored procedures (although it might
be >100) and then run SQL Server Profiler to capture a long running stored
procedures (group by duration).
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fa4401c4584c$6739b350$a101280a@.phx.gbl...[vbcol=seagreen]
> Uri,
> We have 4000 SPs. The table is probably called by
> hundreds of these SPs. Through knowledge of the front-
> ends, we have clustered, non-clustered & composite
> indexes which we have seen improvements in some SPs, but
> we are still experiencing problems. Can I assume that
> SQLProfiler can't do this?
> Thanks,
> Roger
> wrote in message
> whole
> hurting
Tuesday, February 14, 2012
Can't we use variables in OPENQUERY, FREETEXT("@searchstring")?
I'm writing a stored procedure for a keyword search in a Word or PDF
doc which i've done through Index Server and linked the results to SQL
Server.
Part of my stored proc is shown below in which for a FREETEXT keyword
search i'm using a variable "@.searchstring", which i have to, is not
working.
I know it works with hard text but
Is there any way i can use a Variable in OPENQUERIES or is this my DEAD
END?
Can anyone please guide me how to use a variable in FREETEXT
Thanks in Advance
DECLARE @.searchstring varchar(22)
SET @.searchstring = 'aspnet'
SELECT * FROM OPENQUERY(FileSystem,'SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM SCOPE(''
"c:\inetpub\wwwroot\sap-resources\Uploads" '') WHERE
FREETEXT(''@.searchstring'')')Hi,
You need to resort to dynamic SQL, i.e....
SET @.sql = 'SELECT TOP 50 *
FROM (
SELECT DISTINCT
kba.idKBArticle,
[Rank],
Characterization
FROM ( SELECT DISTINCT TOP 50 [FileName],
[Rank],
Characterization
FROM OPENQUERY( lsIndexServer,
''SELECT FileName, Rank, Characterization
FROM TORVERSRVH3.SQLServerUG2..SCOPE() WHERE ' + CASE
WHEN @.OpType='C' THEN 'CONTAINS' ELSE 'FREETEXT' END +
'( '' +
@.SearchKeywords + '' )'' )
WHERE LEFT( Characterization, 12 ) <>
''vti_encoding''
) AS qry
INNER JOIN KBArticle kba ON kba.ArticleFileName =
qry.[FileName]'
REMEMBER!!!!!! ====>>>>>>>
To prevent injection make absolutely sure you replace any single quotes with
2 single quotes...
-- this one fails and is subject to injection...
declare @.searchtext varchar(100)
set @.searchtext = 'tony''s injection'
exec( 'print ''' + @.searchtext + '''' )
go
-- this one works because prevent injection...
declare @.searchtext varchar(100)
set @.searchtext = 'tony''s injection'
set @.searchtext = REPLACE( @.searchtext, '''', ''' )
exec( 'print ''' + @.searchtext + '''' )
go
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137755654.791956.239920@.z14g2000cwz.googlegroups.com...
> I'm writing a stored procedure for a keyword search in a Word or PDF
> doc which i've done through Index Server and linked the results to SQL
> Server.
> Part of my stored proc is shown below in which for a FREETEXT keyword
> search i'm using a variable "@.searchstring", which i have to, is not
> working.
> I know it works with hard text but
> Is there any way i can use a Variable in OPENQUERIES or is this my DEAD
> END?
> Can anyone please guide me how to use a variable in FREETEXT
> Thanks in Advance
> DECLARE @.searchstring varchar(22)
> SET @.searchstring = 'aspnet'
> SELECT * FROM OPENQUERY(FileSystem,'SELECT Directory, FileName,
> DocAuthor, Size, Create, Write, Path FROM SCOPE(''
> "c:\inetpub\wwwroot\sap-resources\Uploads" '') WHERE
> FREETEXT(''@.searchstring'')')
>|||Thanks for your help
i tried using above idea and some other examples.
The code shown below is working perfectly in the analyzer. I want to
create a view with the results
Is it possible ?
Thanks in Advance
DECLARE @.searchstring varchar(22)
SET @.searchstring = 'aspnet'
declare @.strSQL varchar(244)
select @.strSQL='select FileName,Path from scope(''''
"c:\inetpub\wwwroot\sap-resources\Uploads" '''') where contains ('
select @.strSQL=@.strSQL +char(39)+ char(39)+ @.searchstring +char(39)+
char(39)+')'
select @.strSQL='select * from openquery(FileSystem,'+ char(39)+
@.strSQL+ char(39)+ ')'
exec (@.strSQL)
Something like
CREATE VIEW FileSearchResults AS (@.strSQL)
which is not working|||Hi Savvy,
Sorry - you won't be able to create a view for that unless your search
string is hard-coded and never changes.
You could write a stored procedure that accepts the search string as a
parameter.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137764139.455350.325970@.o13g2000cwo.googlegroups.com...
> Thanks for your help
> i tried using above idea and some other examples.
> The code shown below is working perfectly in the analyzer. I want to
> create a view with the results
> Is it possible ?
> Thanks in Advance
>
> DECLARE @.searchstring varchar(22)
> SET @.searchstring = 'aspnet'
> declare @.strSQL varchar(244)
> select @.strSQL='select FileName,Path from scope(''''
> "c:\inetpub\wwwroot\sap-resources\Uploads" '''') where contains ('
> select @.strSQL=@.strSQL +char(39)+ char(39)+ @.searchstring +char(39)+
> char(39)+')'
> select @.strSQL='select * from openquery(FileSystem,'+ char(39)+
> @.strSQL+ char(39)+ ')'
> exec (@.strSQL)
>
> Something like
> CREATE VIEW FileSearchResults AS (@.strSQL)
> which is not working
>|||Thank you very much for your help and time Tony Rogerson
This is my complete stored procedure which is perfectly working when i
hardcore the @.searchstring with the word which doesn't change.
I just want to use a variable working over there. Can u please help me
in this
Thanks in Advance
CREATE PROCEDURE SelectIndexServerCVpaths
(
@.searchstring varchar(100)
)
AS
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
FREETEXT(''''@.searchstring'''')'')')
SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
F.PATH AND C.DefaultID=1
GO|||CREATE PROCEDURE SelectIndexServerCVpaths
(
@.searchstring varchar(100)
)
AS
SET @.searchstring = REPLACE( @.searchstring, '''', ''' )
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
FREETEXT('' + @.searchstring + '')'')')
SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
F.PATH AND C.DefaultID=1
GO
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137766282.280852.185740@.g14g2000cwa.googlegroups.com...
> Thank you very much for your help and time Tony Rogerson
> This is my complete stored procedure which is perfectly working when i
> hardcore the @.searchstring with the word which doesn't change.
> I just want to use a variable working over there. Can u please help me
> in this
> Thanks in Advance
> CREATE PROCEDURE SelectIndexServerCVpaths
> (
> @.searchstring varchar(100)
> )
> AS
> IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
> WHERE TABLE_NAME = 'FileSearchResults')
> DROP VIEW FileSearchResults
> EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
> OPENQUERY(FileSystem,''SELECT Directory, FileName,
> DocAuthor, Size, Create, Write, Path FROM
> SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
> FREETEXT(''''@.searchstring'''')'')')
> SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
> F.PATH AND C.DefaultID=1
> GO
>|||Thanks for your Great help Tony
I have a strange problem its above code is working in the Query
Analyzer but not working if execute the stored procedure as shown below
i tried but i'm not able to figure out where the problem is
Thanks in Advance
Exec SelectIndexServerCVpaths
@.searchstring = 'aspnet'|||whats the error?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137770531.573885.223780@.f14g2000cwb.googlegroups.com...
> Thanks for your Great help Tony
> I have a strange problem its above code is working in the Query
> Analyzer but not working if execute the stored procedure as shown below
> i tried but i'm not able to figure out where the problem is
> Thanks in Advance
> Exec SelectIndexServerCVpaths
> @.searchstring = 'aspnet'
>|||I'm sorry Tony
i didn't copy the code properly in my stored procedure this part
exactly FREETEXT('' + @.searchstring + '')'')')
when i copied again
its working perfectly Tony
You dont know how much your help is worth to me
I cant just express in words
I needed to complete project today which i did with your help
Thank you very very very much Tony Rogerson|||I'm really grateful to you Tony
Thanks onceagain
doc which i've done through Index Server and linked the results to SQL
Server.
Part of my stored proc is shown below in which for a FREETEXT keyword
search i'm using a variable "@.searchstring", which i have to, is not
working.
I know it works with hard text but
Is there any way i can use a Variable in OPENQUERIES or is this my DEAD
END?
Can anyone please guide me how to use a variable in FREETEXT
Thanks in Advance
DECLARE @.searchstring varchar(22)
SET @.searchstring = 'aspnet'
SELECT * FROM OPENQUERY(FileSystem,'SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM SCOPE(''
"c:\inetpub\wwwroot\sap-resources\Uploads" '') WHERE
FREETEXT(''@.searchstring'')')Hi,
You need to resort to dynamic SQL, i.e....
SET @.sql = 'SELECT TOP 50 *
FROM (
SELECT DISTINCT
kba.idKBArticle,
[Rank],
Characterization
FROM ( SELECT DISTINCT TOP 50 [FileName],
[Rank],
Characterization
FROM OPENQUERY( lsIndexServer,
''SELECT FileName, Rank, Characterization
FROM TORVERSRVH3.SQLServerUG2..SCOPE() WHERE ' + CASE
WHEN @.OpType='C' THEN 'CONTAINS' ELSE 'FREETEXT' END +
'( '' +
@.SearchKeywords + '' )'' )
WHERE LEFT( Characterization, 12 ) <>
''vti_encoding''
) AS qry
INNER JOIN KBArticle kba ON kba.ArticleFileName =
qry.[FileName]'
REMEMBER!!!!!! ====>>>>>>>
To prevent injection make absolutely sure you replace any single quotes with
2 single quotes...
-- this one fails and is subject to injection...
declare @.searchtext varchar(100)
set @.searchtext = 'tony''s injection'
exec( 'print ''' + @.searchtext + '''' )
go
-- this one works because prevent injection...
declare @.searchtext varchar(100)
set @.searchtext = 'tony''s injection'
set @.searchtext = REPLACE( @.searchtext, '''', ''' )
exec( 'print ''' + @.searchtext + '''' )
go
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137755654.791956.239920@.z14g2000cwz.googlegroups.com...
> I'm writing a stored procedure for a keyword search in a Word or PDF
> doc which i've done through Index Server and linked the results to SQL
> Server.
> Part of my stored proc is shown below in which for a FREETEXT keyword
> search i'm using a variable "@.searchstring", which i have to, is not
> working.
> I know it works with hard text but
> Is there any way i can use a Variable in OPENQUERIES or is this my DEAD
> END?
> Can anyone please guide me how to use a variable in FREETEXT
> Thanks in Advance
> DECLARE @.searchstring varchar(22)
> SET @.searchstring = 'aspnet'
> SELECT * FROM OPENQUERY(FileSystem,'SELECT Directory, FileName,
> DocAuthor, Size, Create, Write, Path FROM SCOPE(''
> "c:\inetpub\wwwroot\sap-resources\Uploads" '') WHERE
> FREETEXT(''@.searchstring'')')
>|||Thanks for your help
i tried using above idea and some other examples.
The code shown below is working perfectly in the analyzer. I want to
create a view with the results
Is it possible ?
Thanks in Advance
DECLARE @.searchstring varchar(22)
SET @.searchstring = 'aspnet'
declare @.strSQL varchar(244)
select @.strSQL='select FileName,Path from scope(''''
"c:\inetpub\wwwroot\sap-resources\Uploads" '''') where contains ('
select @.strSQL=@.strSQL +char(39)+ char(39)+ @.searchstring +char(39)+
char(39)+')'
select @.strSQL='select * from openquery(FileSystem,'+ char(39)+
@.strSQL+ char(39)+ ')'
exec (@.strSQL)
Something like
CREATE VIEW FileSearchResults AS (@.strSQL)
which is not working|||Hi Savvy,
Sorry - you won't be able to create a view for that unless your search
string is hard-coded and never changes.
You could write a stored procedure that accepts the search string as a
parameter.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137764139.455350.325970@.o13g2000cwo.googlegroups.com...
> Thanks for your help
> i tried using above idea and some other examples.
> The code shown below is working perfectly in the analyzer. I want to
> create a view with the results
> Is it possible ?
> Thanks in Advance
>
> DECLARE @.searchstring varchar(22)
> SET @.searchstring = 'aspnet'
> declare @.strSQL varchar(244)
> select @.strSQL='select FileName,Path from scope(''''
> "c:\inetpub\wwwroot\sap-resources\Uploads" '''') where contains ('
> select @.strSQL=@.strSQL +char(39)+ char(39)+ @.searchstring +char(39)+
> char(39)+')'
> select @.strSQL='select * from openquery(FileSystem,'+ char(39)+
> @.strSQL+ char(39)+ ')'
> exec (@.strSQL)
>
> Something like
> CREATE VIEW FileSearchResults AS (@.strSQL)
> which is not working
>|||Thank you very much for your help and time Tony Rogerson
This is my complete stored procedure which is perfectly working when i
hardcore the @.searchstring with the word which doesn't change.
I just want to use a variable working over there. Can u please help me
in this
Thanks in Advance
CREATE PROCEDURE SelectIndexServerCVpaths
(
@.searchstring varchar(100)
)
AS
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
FREETEXT(''''@.searchstring'''')'')')
SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
F.PATH AND C.DefaultID=1
GO|||CREATE PROCEDURE SelectIndexServerCVpaths
(
@.searchstring varchar(100)
)
AS
SET @.searchstring = REPLACE( @.searchstring, '''', ''' )
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
FREETEXT('' + @.searchstring + '')'')')
SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
F.PATH AND C.DefaultID=1
GO
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137766282.280852.185740@.g14g2000cwa.googlegroups.com...
> Thank you very much for your help and time Tony Rogerson
> This is my complete stored procedure which is perfectly working when i
> hardcore the @.searchstring with the word which doesn't change.
> I just want to use a variable working over there. Can u please help me
> in this
> Thanks in Advance
> CREATE PROCEDURE SelectIndexServerCVpaths
> (
> @.searchstring varchar(100)
> )
> AS
> IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
> WHERE TABLE_NAME = 'FileSearchResults')
> DROP VIEW FileSearchResults
> EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
> OPENQUERY(FileSystem,''SELECT Directory, FileName,
> DocAuthor, Size, Create, Write, Path FROM
> SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
> FREETEXT(''''@.searchstring'''')'')')
> SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
> F.PATH AND C.DefaultID=1
> GO
>|||Thanks for your Great help Tony
I have a strange problem its above code is working in the Query
Analyzer but not working if execute the stored procedure as shown below
i tried but i'm not able to figure out where the problem is
Thanks in Advance
Exec SelectIndexServerCVpaths
@.searchstring = 'aspnet'|||whats the error?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137770531.573885.223780@.f14g2000cwb.googlegroups.com...
> Thanks for your Great help Tony
> I have a strange problem its above code is working in the Query
> Analyzer but not working if execute the stored procedure as shown below
> i tried but i'm not able to figure out where the problem is
> Thanks in Advance
> Exec SelectIndexServerCVpaths
> @.searchstring = 'aspnet'
>|||I'm sorry Tony
i didn't copy the code properly in my stored procedure this part
exactly FREETEXT('' + @.searchstring + '')'')')
when i copied again
its working perfectly Tony
You dont know how much your help is worth to me
I cant just express in words
I needed to complete project today which i did with your help
Thank you very very very much Tony Rogerson|||I'm really grateful to you Tony
Thanks onceagain
can't use the index tuning wizard wioth a function??
Hi,
I receive this error when I try to execute the index tuning wizard:
"There are no events in the workload. Either the trace
file contained no SQL batch or RPC events or the SQL
script contained no SQL queries."
I have tried from the query analyzer and from a workload trace file, in the
2 cases I receive the error.
My query contain a join to a custom function which return a simple list.
if I remove the function, then the index tuning works fine.
my query:
select * from table1 inner join dbo.MyFunction(@.Param) A on table1.ID =
A.ID
what can I do?
thanks.
Jerome.
Jj wrote:
> Hi,
> I receive this error when I try to execute the index tuning wizard:
> "There are no events in the workload. Either the trace
> file contained no SQL batch or RPC events or the SQL
> script contained no SQL queries."
> I have tried from the query analyzer and from a workload trace file,
> in the 2 cases I receive the error.
> My query contain a join to a custom function which return a simple
> list. if I remove the function, then the index tuning works fine.
> my query:
> select * from table1 inner join dbo.MyFunction(@.Param) A on
> table1.ID = A.ID
> what can I do?
> thanks.
> Jerome.
You probably chose the wrong template for recording of events in profiler.
There is a template SQLProfilerTuning. It should work with that one.
Kind regards
robert
|||I'm using standard templates which works fine with any query those with my
function.
but why I can't optimize from query analyzer?
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:e$XXF4wcFHA.2760@.tk2msftngp13.phx.gbl...
> Jj wrote:
> You probably chose the wrong template for recording of events in profiler.
> There is a template SQLProfilerTuning. It should work with that one.
> Kind regards
> robert
>
I receive this error when I try to execute the index tuning wizard:
"There are no events in the workload. Either the trace
file contained no SQL batch or RPC events or the SQL
script contained no SQL queries."
I have tried from the query analyzer and from a workload trace file, in the
2 cases I receive the error.
My query contain a join to a custom function which return a simple list.
if I remove the function, then the index tuning works fine.
my query:
select * from table1 inner join dbo.MyFunction(@.Param) A on table1.ID =
A.ID
what can I do?
thanks.
Jerome.
Jj wrote:
> Hi,
> I receive this error when I try to execute the index tuning wizard:
> "There are no events in the workload. Either the trace
> file contained no SQL batch or RPC events or the SQL
> script contained no SQL queries."
> I have tried from the query analyzer and from a workload trace file,
> in the 2 cases I receive the error.
> My query contain a join to a custom function which return a simple
> list. if I remove the function, then the index tuning works fine.
> my query:
> select * from table1 inner join dbo.MyFunction(@.Param) A on
> table1.ID = A.ID
> what can I do?
> thanks.
> Jerome.
You probably chose the wrong template for recording of events in profiler.
There is a template SQLProfilerTuning. It should work with that one.
Kind regards
robert
|||I'm using standard templates which works fine with any query those with my
function.
but why I can't optimize from query analyzer?
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:e$XXF4wcFHA.2760@.tk2msftngp13.phx.gbl...
> Jj wrote:
> You probably chose the wrong template for recording of events in profiler.
> There is a template SQLProfilerTuning. It should work with that one.
> Kind regards
> robert
>
can't use the index tuning wizard wioth a function??
Hi,
I receive this error when I try to execute the index tuning wizard:
"There are no events in the workload. Either the trace
file contained no SQL batch or RPC events or the SQL
script contained no SQL queries."
I have tried from the query analyzer and from a workload trace file, in the
2 cases I receive the error.
My query contain a join to a custom function which return a simple list.
if I remove the function, then the index tuning works fine.
my query:
select * from table1 inner join dbo.MyFunction(@.Param) A on table1.ID =
A.ID
what can I do?
thanks.
Jerome.Jj wrote:
> Hi,
> I receive this error when I try to execute the index tuning wizard:
> "There are no events in the workload. Either the trace
> file contained no SQL batch or RPC events or the SQL
> script contained no SQL queries."
> I have tried from the query analyzer and from a workload trace file,
> in the 2 cases I receive the error.
> My query contain a join to a custom function which return a simple
> list. if I remove the function, then the index tuning works fine.
> my query:
> select * from table1 inner join dbo.MyFunction(@.Param) A on
> table1.ID = A.ID
> what can I do?
> thanks.
> Jerome.
You probably chose the wrong template for recording of events in profiler.
There is a template SQLProfilerTuning. It should work with that one.
Kind regards
robert|||I'm using standard templates which works fine with any query those with my
function.
but why I can't optimize from query analyzer?
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:e$XXF4wcFHA.2760@.tk2msftngp13.phx.gbl...
> Jj wrote:
> You probably chose the wrong template for recording of events in profiler.
> There is a template SQLProfilerTuning. It should work with that one.
> Kind regards
> robert
>
I receive this error when I try to execute the index tuning wizard:
"There are no events in the workload. Either the trace
file contained no SQL batch or RPC events or the SQL
script contained no SQL queries."
I have tried from the query analyzer and from a workload trace file, in the
2 cases I receive the error.
My query contain a join to a custom function which return a simple list.
if I remove the function, then the index tuning works fine.
my query:
select * from table1 inner join dbo.MyFunction(@.Param) A on table1.ID =
A.ID
what can I do?
thanks.
Jerome.Jj wrote:
> Hi,
> I receive this error when I try to execute the index tuning wizard:
> "There are no events in the workload. Either the trace
> file contained no SQL batch or RPC events or the SQL
> script contained no SQL queries."
> I have tried from the query analyzer and from a workload trace file,
> in the 2 cases I receive the error.
> My query contain a join to a custom function which return a simple
> list. if I remove the function, then the index tuning works fine.
> my query:
> select * from table1 inner join dbo.MyFunction(@.Param) A on
> table1.ID = A.ID
> what can I do?
> thanks.
> Jerome.
You probably chose the wrong template for recording of events in profiler.
There is a template SQLProfilerTuning. It should work with that one.
Kind regards
robert|||I'm using standard templates which works fine with any query those with my
function.
but why I can't optimize from query analyzer?
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:e$XXF4wcFHA.2760@.tk2msftngp13.phx.gbl...
> Jj wrote:
> You probably chose the wrong template for recording of events in profiler.
> There is a template SQLProfilerTuning. It should work with that one.
> Kind regards
> robert
>
can't use the index tuning wizard wioth a function??
Hi,
I receive this error when I try to execute the index tuning wizard:
"There are no events in the workload. Either the trace
file contained no SQL batch or RPC events or the SQL
script contained no SQL queries."
I have tried from the query analyzer and from a workload trace file, in the
2 cases I receive the error.
My query contain a join to a custom function which return a simple list.
if I remove the function, then the index tuning works fine.
my query:
select * from table1 inner join dbo.MyFunction(@.Param) A on table1.ID = A.ID
what can I do?
thanks.
Jerome.Jéjé wrote:
> Hi,
> I receive this error when I try to execute the index tuning wizard:
> "There are no events in the workload. Either the trace
> file contained no SQL batch or RPC events or the SQL
> script contained no SQL queries."
> I have tried from the query analyzer and from a workload trace file,
> in the 2 cases I receive the error.
> My query contain a join to a custom function which return a simple
> list. if I remove the function, then the index tuning works fine.
> my query:
> select * from table1 inner join dbo.MyFunction(@.Param) A on
> table1.ID = A.ID
> what can I do?
> thanks.
> Jerome.
You probably chose the wrong template for recording of events in profiler.
There is a template SQLProfilerTuning. It should work with that one.
Kind regards
robert|||I'm using standard templates which works fine with any query those with my
function.
but why I can't optimize from query analyzer?
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:e$XXF4wcFHA.2760@.tk2msftngp13.phx.gbl...
> Jéjé wrote:
>> Hi,
>> I receive this error when I try to execute the index tuning wizard:
>> "There are no events in the workload. Either the trace
>> file contained no SQL batch or RPC events or the SQL
>> script contained no SQL queries."
>> I have tried from the query analyzer and from a workload trace file,
>> in the 2 cases I receive the error.
>> My query contain a join to a custom function which return a simple
>> list. if I remove the function, then the index tuning works fine.
>> my query:
>> select * from table1 inner join dbo.MyFunction(@.Param) A on
>> table1.ID = A.ID
>> what can I do?
>> thanks.
>> Jerome.
> You probably chose the wrong template for recording of events in profiler.
> There is a template SQLProfilerTuning. It should work with that one.
> Kind regards
> robert
>
I receive this error when I try to execute the index tuning wizard:
"There are no events in the workload. Either the trace
file contained no SQL batch or RPC events or the SQL
script contained no SQL queries."
I have tried from the query analyzer and from a workload trace file, in the
2 cases I receive the error.
My query contain a join to a custom function which return a simple list.
if I remove the function, then the index tuning works fine.
my query:
select * from table1 inner join dbo.MyFunction(@.Param) A on table1.ID = A.ID
what can I do?
thanks.
Jerome.Jéjé wrote:
> Hi,
> I receive this error when I try to execute the index tuning wizard:
> "There are no events in the workload. Either the trace
> file contained no SQL batch or RPC events or the SQL
> script contained no SQL queries."
> I have tried from the query analyzer and from a workload trace file,
> in the 2 cases I receive the error.
> My query contain a join to a custom function which return a simple
> list. if I remove the function, then the index tuning works fine.
> my query:
> select * from table1 inner join dbo.MyFunction(@.Param) A on
> table1.ID = A.ID
> what can I do?
> thanks.
> Jerome.
You probably chose the wrong template for recording of events in profiler.
There is a template SQLProfilerTuning. It should work with that one.
Kind regards
robert|||I'm using standard templates which works fine with any query those with my
function.
but why I can't optimize from query analyzer?
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:e$XXF4wcFHA.2760@.tk2msftngp13.phx.gbl...
> Jéjé wrote:
>> Hi,
>> I receive this error when I try to execute the index tuning wizard:
>> "There are no events in the workload. Either the trace
>> file contained no SQL batch or RPC events or the SQL
>> script contained no SQL queries."
>> I have tried from the query analyzer and from a workload trace file,
>> in the 2 cases I receive the error.
>> My query contain a join to a custom function which return a simple
>> list. if I remove the function, then the index tuning works fine.
>> my query:
>> select * from table1 inner join dbo.MyFunction(@.Param) A on
>> table1.ID = A.ID
>> what can I do?
>> thanks.
>> Jerome.
> You probably chose the wrong template for recording of events in profiler.
> There is a template SQLProfilerTuning. It should work with that one.
> Kind regards
> robert
>
Subscribe to:
Posts (Atom)