Hi,
I want to bring up the following data for groups:
Blue, Red, Green and Yellow.
I have the following view in sql 7 which brings up the data i want, How can
i bring up the data for the other groups within the same query.
SELECT DISTINCT
salesorders.srep, SUM(salesitems.sprice) AS Expr1,
delv.dtaxd
FROM dbo.salesorders INNER JOIN
dbo.salesitems ON
dbo.salesorders.son = dbo.salesitems.sona INNER JOIN
dbo.delvitems ON
dbo.salesorders.son = dbo.delvitems.dord AND
dbo.salesitems.sonitem = dbo.delvitems.ditem INNER JOIN
dbo.delv ON
dbo.delvitems.delvnoa = dbo.delv.delvno
WHERE (dbo.salesorders.srep = 'blue') AND
(dbo.delv.dedate > CONVERT(DATETIME,
'2008-02-01 00:00:00', 102))
GROUP BY dbo.salesorders.srep, dbo.delv.dtaxd
I then need to call this query witin MS Access and use it to output to a
Data sheet.
Thanks
Mohammad
A little more info.
The data i want will look like this as an example:
Team price Date
==== ==== ===
Blue 500 01/01/2008
Green 600 04/02/2008
Yellow 2000 01/02/2008
"mahmad" wrote:
> Hi,
> I want to bring up the following data for groups:
> Blue, Red, Green and Yellow.
> I have the following view in sql 7 which brings up the data i want, How can
> i bring up the data for the other groups within the same query.
> SELECT DISTINCT
> salesorders.srep, SUM(salesitems.sprice) AS Expr1,
> delv.dtaxd
> FROM dbo.salesorders INNER JOIN
> dbo.salesitems ON
> dbo.salesorders.son = dbo.salesitems.sona INNER JOIN
> dbo.delvitems ON
> dbo.salesorders.son = dbo.delvitems.dord AND
> dbo.salesitems.sonitem = dbo.delvitems.ditem INNER JOIN
> dbo.delv ON
> dbo.delvitems.delvnoa = dbo.delv.delvno
> WHERE (dbo.salesorders.srep = 'blue') AND
> (dbo.delv.dedate > CONVERT(DATETIME,
> '2008-02-01 00:00:00', 102))
> GROUP BY dbo.salesorders.srep, dbo.delv.dtaxd
> I then need to call this query witin MS Access and use it to output to a
> Data sheet.
> Thanks
> Mohammad
Showing posts with label view. Show all posts
Showing posts with label view. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
query help
Hi,
I want to bring up the following data for groups:
Blue, Red, Green and Yellow.
I have the following view in sql 7 which brings up the data i want, How can
i bring up the data for the other groups within the same query.
SELECT DISTINCT
salesorders.srep, SUM(salesitems.sprice) AS Expr1,
delv.dtaxd
FROM dbo.salesorders INNER JOIN
dbo.salesitems ON
dbo.salesorders.son = dbo.salesitems.sona INNER JOIN
dbo.delvitems ON
dbo.salesorders.son = dbo.delvitems.dord AND
dbo.salesitems.sonitem = dbo.delvitems.ditem INNER JOIN
dbo.delv ON
dbo.delvitems.delvnoa = dbo.delv.delvno
WHERE (dbo.salesorders.srep = 'blue') AND
(dbo.delv.dedate > CONVERT(DATETIME,
'2008-02-01 00:00:00', 102))
GROUP BY dbo.salesorders.srep, dbo.delv.dtaxd
I then need to call this query witin MS Access and use it to output to a
Data sheet.
Thanks
MohammadA little more info.
The data i want will look like this as an example:
Team price Date
==== ==== ===Blue 500 01/01/2008
Green 600 04/02/2008
Yellow 2000 01/02/2008
"mahmad" wrote:
> Hi,
> I want to bring up the following data for groups:
> Blue, Red, Green and Yellow.
> I have the following view in sql 7 which brings up the data i want, How can
> i bring up the data for the other groups within the same query.
> SELECT DISTINCT
> salesorders.srep, SUM(salesitems.sprice) AS Expr1,
> delv.dtaxd
> FROM dbo.salesorders INNER JOIN
> dbo.salesitems ON
> dbo.salesorders.son = dbo.salesitems.sona INNER JOIN
> dbo.delvitems ON
> dbo.salesorders.son = dbo.delvitems.dord AND
> dbo.salesitems.sonitem = dbo.delvitems.ditem INNER JOIN
> dbo.delv ON
> dbo.delvitems.delvnoa = dbo.delv.delvno
> WHERE (dbo.salesorders.srep = 'blue') AND
> (dbo.delv.dedate > CONVERT(DATETIME,
> '2008-02-01 00:00:00', 102))
> GROUP BY dbo.salesorders.srep, dbo.delv.dtaxd
> I then need to call this query witin MS Access and use it to output to a
> Data sheet.
> Thanks
> Mohammadsql
I want to bring up the following data for groups:
Blue, Red, Green and Yellow.
I have the following view in sql 7 which brings up the data i want, How can
i bring up the data for the other groups within the same query.
SELECT DISTINCT
salesorders.srep, SUM(salesitems.sprice) AS Expr1,
delv.dtaxd
FROM dbo.salesorders INNER JOIN
dbo.salesitems ON
dbo.salesorders.son = dbo.salesitems.sona INNER JOIN
dbo.delvitems ON
dbo.salesorders.son = dbo.delvitems.dord AND
dbo.salesitems.sonitem = dbo.delvitems.ditem INNER JOIN
dbo.delv ON
dbo.delvitems.delvnoa = dbo.delv.delvno
WHERE (dbo.salesorders.srep = 'blue') AND
(dbo.delv.dedate > CONVERT(DATETIME,
'2008-02-01 00:00:00', 102))
GROUP BY dbo.salesorders.srep, dbo.delv.dtaxd
I then need to call this query witin MS Access and use it to output to a
Data sheet.
Thanks
MohammadA little more info.
The data i want will look like this as an example:
Team price Date
==== ==== ===Blue 500 01/01/2008
Green 600 04/02/2008
Yellow 2000 01/02/2008
"mahmad" wrote:
> Hi,
> I want to bring up the following data for groups:
> Blue, Red, Green and Yellow.
> I have the following view in sql 7 which brings up the data i want, How can
> i bring up the data for the other groups within the same query.
> SELECT DISTINCT
> salesorders.srep, SUM(salesitems.sprice) AS Expr1,
> delv.dtaxd
> FROM dbo.salesorders INNER JOIN
> dbo.salesitems ON
> dbo.salesorders.son = dbo.salesitems.sona INNER JOIN
> dbo.delvitems ON
> dbo.salesorders.son = dbo.delvitems.dord AND
> dbo.salesitems.sonitem = dbo.delvitems.ditem INNER JOIN
> dbo.delv ON
> dbo.delvitems.delvnoa = dbo.delv.delvno
> WHERE (dbo.salesorders.srep = 'blue') AND
> (dbo.delv.dedate > CONVERT(DATETIME,
> '2008-02-01 00:00:00', 102))
> GROUP BY dbo.salesorders.srep, dbo.delv.dtaxd
> I then need to call this query witin MS Access and use it to output to a
> Data sheet.
> Thanks
> Mohammadsql
Monday, March 26, 2012
query gives out different results
Hi,
I did post a similar question yesterday - but it seems to have disappeared
from view, so appologies if you have seen this before.
I originally had a program which I sas told to put into a stored procedure.
I have done this BUT, to check that it's working I'm running them against
each other BUT and getting different results, in the final output. I do know
that the query is producing the correct result. I think the only difference
is I have a GO statement in the original program, between where the pquery i
s
run and the query updates the main table.
There are a number of steps to this process.
1. I copy the DISTINCT ref to a new output table (This works OK, and creates
538332 records)
2. I add another column to the output table called TENURE (This also works O
K)
3. I exec the query which has two parts to it. The first part runs a query
which puts the DISTINCT ref into a temporary table (This works ok and create
s
64379 records - as does the original code)
This but looks like:
CREATE TABLE #URN_STEP05
(REF varchar(255))
INSERT URN_STEP05(ref)
SELECT DISTINCT ref
FROM U_NFADHOC
WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
-12,CONVERT(DATETIME,@.enddate))) AND CONVERT(DATETIME, @.enddate)
AND RESPTYPE='NF Cash Donation'
AND ref IN(SELECT ref
FROM U_NFADHOC
WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
-24,CONVERT(DATETIME,@.enddate))) AND DATEADD(MONTH,
-12,CONVERT(DATETIME,@.enddate))
AND RESPTYPE='NF Cash Donation')
ORDER BY ref
5. The next bit is copying 70519 records into the output file. I can't work
out why.
UPDATE U_tenure
SET TENURE = 'CORE'
FROM U_tenure
INNER JOIN URN_STEP05
ON U_tenure.REF = URN_STEP05.REF
WHERE U_tenure.REF = URN_STEP05.REF
Is there something I'm doing wrong here? Is it possible to UPDATE the
original table instead of using a temporary table first?
Any help would be appreciated
RobWhy you are creating a temporary table #URN_STEP05
and referencing a permanent table URN_STEP05
in your insert and update statements?
I guess that might be the problem
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:E9433D93-A679-441A-B6E2-F6F566D8BC54@.microsoft.com...
> Hi,
> I did post a similar question yesterday - but it seems to have disappeared
> from view, so appologies if you have seen this before.
> I originally had a program which I sas told to put into a stored
> procedure.
> I have done this BUT, to check that it's working I'm running them against
> each other BUT and getting different results, in the final output. I do
> know
> that the query is producing the correct result. I think the only
> difference
> is I have a GO statement in the original program, between where the pquery
> is
> run and the query updates the main table.
> There are a number of steps to this process.
> 1. I copy the DISTINCT ref to a new output table (This works OK, and
> creates
> 538332 records)
> 2. I add another column to the output table called TENURE (This also works
> OK)
> 3. I exec the query which has two parts to it. The first part runs a query
> which puts the DISTINCT ref into a temporary table (This works ok and
> creates
> 64379 records - as does the original code)
> This but looks like:
> CREATE TABLE #URN_STEP05
> (REF varchar(255))
> INSERT URN_STEP05(ref)
> SELECT DISTINCT ref
> FROM U_NFADHOC
> WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
> -12,CONVERT(DATETIME,@.enddate))) AND CONVERT(DATETIME, @.enddate)
> AND RESPTYPE='NF Cash Donation'
> AND ref IN(SELECT ref
> FROM U_NFADHOC
> WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
> -24,CONVERT(DATETIME,@.enddate))) AND DATEADD(MONTH,
> -12,CONVERT(DATETIME,@.enddate))
> AND RESPTYPE='NF Cash Donation')
> ORDER BY ref
> 5. The next bit is copying 70519 records into the output file. I can't
> work
> out why.
> UPDATE U_tenure
> SET TENURE = 'CORE'
> FROM U_tenure
> INNER JOIN URN_STEP05
> ON U_tenure.REF = URN_STEP05.REF
> WHERE U_tenure.REF = URN_STEP05.REF
> --
> Is there something I'm doing wrong here? Is it possible to UPDATE the
> original table instead of using a temporary table first?
> Any help would be appreciated
> Rob
>
>|||Hi Roji,
I am not sure how to put the output direct into the table - when I tried
that it copied CORE to all of the TENURE fields instead of just the 64379
records. Is there a way to put them in directly? If so that would be a lot
better. Can you possably show me how to do this please?
Regards
Rob
"Roji. P. Thomas" wrote:
> Why you are creating a temporary table #URN_STEP05
> and referencing a permanent table URN_STEP05
> in your insert and update statements?
> I guess that might be the problem
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:E9433D93-A679-441A-B6E2-F6F566D8BC54@.microsoft.com...
>
>|||Did you read both replies in yesterday's thread?
http://www.google.co.uk/groups?hl=e...40microsoft.com
> 5. The next bit is copying 70519 records into the output file. I
can't work
out why.
As previously stated, UPDATE doesn't "copy records". Your UPDATE will
set the value of Tenure on every row whose Ref is in URN_STEP05. If
that's not what you want then please post enough code actually to
reproduce the problem: DDL, sample data INSERTs and show your required
end result from that sample (not all 70,000 rows - just a few rows but
enough to illustrate the problem.)
David Portas
SQL Server MVP
--
I did post a similar question yesterday - but it seems to have disappeared
from view, so appologies if you have seen this before.
I originally had a program which I sas told to put into a stored procedure.
I have done this BUT, to check that it's working I'm running them against
each other BUT and getting different results, in the final output. I do know
that the query is producing the correct result. I think the only difference
is I have a GO statement in the original program, between where the pquery i
s
run and the query updates the main table.
There are a number of steps to this process.
1. I copy the DISTINCT ref to a new output table (This works OK, and creates
538332 records)
2. I add another column to the output table called TENURE (This also works O
K)
3. I exec the query which has two parts to it. The first part runs a query
which puts the DISTINCT ref into a temporary table (This works ok and create
s
64379 records - as does the original code)
This but looks like:
CREATE TABLE #URN_STEP05
(REF varchar(255))
INSERT URN_STEP05(ref)
SELECT DISTINCT ref
FROM U_NFADHOC
WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
-12,CONVERT(DATETIME,@.enddate))) AND CONVERT(DATETIME, @.enddate)
AND RESPTYPE='NF Cash Donation'
AND ref IN(SELECT ref
FROM U_NFADHOC
WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
-24,CONVERT(DATETIME,@.enddate))) AND DATEADD(MONTH,
-12,CONVERT(DATETIME,@.enddate))
AND RESPTYPE='NF Cash Donation')
ORDER BY ref
5. The next bit is copying 70519 records into the output file. I can't work
out why.
UPDATE U_tenure
SET TENURE = 'CORE'
FROM U_tenure
INNER JOIN URN_STEP05
ON U_tenure.REF = URN_STEP05.REF
WHERE U_tenure.REF = URN_STEP05.REF
Is there something I'm doing wrong here? Is it possible to UPDATE the
original table instead of using a temporary table first?
Any help would be appreciated
RobWhy you are creating a temporary table #URN_STEP05
and referencing a permanent table URN_STEP05
in your insert and update statements?
I guess that might be the problem
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:E9433D93-A679-441A-B6E2-F6F566D8BC54@.microsoft.com...
> Hi,
> I did post a similar question yesterday - but it seems to have disappeared
> from view, so appologies if you have seen this before.
> I originally had a program which I sas told to put into a stored
> procedure.
> I have done this BUT, to check that it's working I'm running them against
> each other BUT and getting different results, in the final output. I do
> know
> that the query is producing the correct result. I think the only
> difference
> is I have a GO statement in the original program, between where the pquery
> is
> run and the query updates the main table.
> There are a number of steps to this process.
> 1. I copy the DISTINCT ref to a new output table (This works OK, and
> creates
> 538332 records)
> 2. I add another column to the output table called TENURE (This also works
> OK)
> 3. I exec the query which has two parts to it. The first part runs a query
> which puts the DISTINCT ref into a temporary table (This works ok and
> creates
> 64379 records - as does the original code)
> This but looks like:
> CREATE TABLE #URN_STEP05
> (REF varchar(255))
> INSERT URN_STEP05(ref)
> SELECT DISTINCT ref
> FROM U_NFADHOC
> WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
> -12,CONVERT(DATETIME,@.enddate))) AND CONVERT(DATETIME, @.enddate)
> AND RESPTYPE='NF Cash Donation'
> AND ref IN(SELECT ref
> FROM U_NFADHOC
> WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
> -24,CONVERT(DATETIME,@.enddate))) AND DATEADD(MONTH,
> -12,CONVERT(DATETIME,@.enddate))
> AND RESPTYPE='NF Cash Donation')
> ORDER BY ref
> 5. The next bit is copying 70519 records into the output file. I can't
> work
> out why.
> UPDATE U_tenure
> SET TENURE = 'CORE'
> FROM U_tenure
> INNER JOIN URN_STEP05
> ON U_tenure.REF = URN_STEP05.REF
> WHERE U_tenure.REF = URN_STEP05.REF
> --
> Is there something I'm doing wrong here? Is it possible to UPDATE the
> original table instead of using a temporary table first?
> Any help would be appreciated
> Rob
>
>|||Hi Roji,
I am not sure how to put the output direct into the table - when I tried
that it copied CORE to all of the TENURE fields instead of just the 64379
records. Is there a way to put them in directly? If so that would be a lot
better. Can you possably show me how to do this please?
Regards
Rob
"Roji. P. Thomas" wrote:
> Why you are creating a temporary table #URN_STEP05
> and referencing a permanent table URN_STEP05
> in your insert and update statements?
> I guess that might be the problem
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:E9433D93-A679-441A-B6E2-F6F566D8BC54@.microsoft.com...
>
>|||Did you read both replies in yesterday's thread?
http://www.google.co.uk/groups?hl=e...40microsoft.com
> 5. The next bit is copying 70519 records into the output file. I
can't work
out why.
As previously stated, UPDATE doesn't "copy records". Your UPDATE will
set the value of Tenure on every row whose Ref is in URN_STEP05. If
that's not what you want then please post enough code actually to
reproduce the problem: DDL, sample data INSERTs and show your required
end result from that sample (not all 70,000 rows - just a few rows but
enough to illustrate the problem.)
David Portas
SQL Server MVP
--
query function in stored proc... how to do it?
how can i view all the records in my stored procedure?
how is the query function done?
for example i had my datagrid view... and of course a view button that will trigger a view action in able to view the entire records that i input....
i am not familiar with such things..
pls help me to figure this out..
thanks..
im just a begginer when it comes to this..
pls help me..
thanks..
Are you asking about how to do this from your application code or from toosl like SSMS ?Jens K. Suessmeyer.
http://www.sqlserver2005.de
Wednesday, March 21, 2012
Query for column name and type
hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
Sridhar
You could use sp_help.
Also consider INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS.
See SQL Server 2000 Books Online for more information.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
Sridhar
|||Hi,
Execute the below query, replace the object_name with your object name
required.
select TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.columns
where table_name='object_name'
Tahnks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
> hi,
> can some one give me a query which retrieves column name ,
> and it's type ,by taking input as a table or view name..
> Sridhar
|||Thanks to you all...
>--Original Message--
>You could use sp_help.
>Also consider INFORMATION_SCHEMA.TABLES and
INFORMATION_SCHEMA.COLUMNS.
>See SQL Server 2000 Books Online for more information.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:54b501c42d17$90dce570$a401280a@.phx.gbl...
>hi,
>can some one give me a query which retrieves column name ,
>and it's type ,by taking input as a table or view name..
>Sridhar
>
>.
>
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
Sridhar
You could use sp_help.
Also consider INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS.
See SQL Server 2000 Books Online for more information.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
Sridhar
|||Hi,
Execute the below query, replace the object_name with your object name
required.
select TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.columns
where table_name='object_name'
Tahnks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
> hi,
> can some one give me a query which retrieves column name ,
> and it's type ,by taking input as a table or view name..
> Sridhar
|||Thanks to you all...
>--Original Message--
>You could use sp_help.
>Also consider INFORMATION_SCHEMA.TABLES and
INFORMATION_SCHEMA.COLUMNS.
>See SQL Server 2000 Books Online for more information.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:54b501c42d17$90dce570$a401280a@.phx.gbl...
>hi,
>can some one give me a query which retrieves column name ,
>and it's type ,by taking input as a table or view name..
>Sridhar
>
>.
>
Query for column name and type
hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
SridharYou could use sp_help.
Also consider INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS.
See SQL Server 2000 Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
Sridhar|||Hi,
Execute the below query, replace the object_name with your object name
required.
select TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.columns
where table_name='object_name'
Tahnks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
> hi,
> can some one give me a query which retrieves column name ,
> and it's type ,by taking input as a table or view name..
> Sridhar|||Thanks to you all...
>--Original Message--
>You could use sp_help.
>Also consider INFORMATION_SCHEMA.TABLES and
INFORMATION_SCHEMA.COLUMNS.
>See SQL Server 2000 Books Online for more information.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:54b501c42d17$90dce570$a401280a@.phx.gbl...
>hi,
>can some one give me a query which retrieves column name ,
>and it's type ,by taking input as a table or view name..
>Sridhar
>
>.
>
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
SridharYou could use sp_help.
Also consider INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS.
See SQL Server 2000 Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
Sridhar|||Hi,
Execute the below query, replace the object_name with your object name
required.
select TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.columns
where table_name='object_name'
Tahnks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
> hi,
> can some one give me a query which retrieves column name ,
> and it's type ,by taking input as a table or view name..
> Sridhar|||Thanks to you all...
>--Original Message--
>You could use sp_help.
>Also consider INFORMATION_SCHEMA.TABLES and
INFORMATION_SCHEMA.COLUMNS.
>See SQL Server 2000 Books Online for more information.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:54b501c42d17$90dce570$a401280a@.phx.gbl...
>hi,
>can some one give me a query which retrieves column name ,
>and it's type ,by taking input as a table or view name..
>Sridhar
>
>.
>
Query for column name and type
hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
SridharYou could use sp_help.
Also consider INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS.
See SQL Server 2000 Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
Sridhar|||Hi,
Execute the below query, replace the object_name with your object name
required.
select TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.columns
where table_name='object_name'
Tahnks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
> hi,
> can some one give me a query which retrieves column name ,
> and it's type ,by taking input as a table or view name..
> Sridhar|||Thanks to you all...
>--Original Message--
>You could use sp_help.
>Also consider INFORMATION_SCHEMA.TABLES and
INFORMATION_SCHEMA.COLUMNS.
>See SQL Server 2000 Books Online for more information.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:54b501c42d17$90dce570$a401280a@.phx.gbl...
>hi,
>can some one give me a query which retrieves column name ,
>and it's type ,by taking input as a table or view name..
>Sridhar
>
>.
>sql
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
SridharYou could use sp_help.
Also consider INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS.
See SQL Server 2000 Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
Sridhar|||Hi,
Execute the below query, replace the object_name with your object name
required.
select TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.columns
where table_name='object_name'
Tahnks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
> hi,
> can some one give me a query which retrieves column name ,
> and it's type ,by taking input as a table or view name..
> Sridhar|||Thanks to you all...
>--Original Message--
>You could use sp_help.
>Also consider INFORMATION_SCHEMA.TABLES and
INFORMATION_SCHEMA.COLUMNS.
>See SQL Server 2000 Books Online for more information.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:54b501c42d17$90dce570$a401280a@.phx.gbl...
>hi,
>can some one give me a query which retrieves column name ,
>and it's type ,by taking input as a table or view name..
>Sridhar
>
>.
>sql
query for a view
Hello,
I have a View called View1 with the field ID, F1, F2, F3. Now I need to check if (Total F1 < Total F2 + Total F3) per ID, if yes fetch all records (so if condition matches, I need to bring rows, not only totals) how can I write my view query to handle this?
Thanks,
I have a View called View1 with the field ID, F1, F2, F3. Now I need to check if (Total F1 < Total F2 + Total F3) per ID, if yes fetch all records (so if condition matches, I need to bring rows, not only totals) how can I write my view query to handle this?
Thanks,
SELECT *
FROM view1 v
WHERE id IN (SELECT id FROM view1 WHERE id = v.id AND SUM(f1) < SUM(f1)+SUM(f2) GROUP BY id)
Nick
Query execution takes different times from different clients
Hi everyone,
first of all, i hope this is the correct newsgroup for this question
I've hit a scenario where I'm execuing a view from inside some .NET code.
I've set a timeout of 10 minutes, although this should be more than enough.
However, the query times out. When i connect to the database using SQL
Server Management Studio (full version) and execute the same view it takes 4
seconds to return the 84 rows to me that i'm expecting... Now, i've never
experienced this kind of issue before so i've no idea what could be causing
it. Some other queries are run before it and they finish fine (although a
little slower than i would expect perhaps).I've also used MS Access to try
and run the same view and I get the same problem as from code.
Could there be any client settings that could cause this kind of difference?
I'd appreciate anyones thoughts on this,
Thanks,
Andrew
Try turning on SQL Server Profiler and see if SQL Server is really receiving
the same query from both sources. You can also check the query execution
command to double check the performance.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:Ou7KiNcNIHA.4832@.TK2MSFTNGP04.phx.gbl...
> Hi everyone,
> first of all, i hope this is the correct newsgroup for this question
> I've hit a scenario where I'm execuing a view from inside some .NET code.
> I've set a timeout of 10 minutes, although this should be more than
> enough. However, the query times out. When i connect to the database using
> SQL Server Management Studio (full version) and execute the same view it
> takes 4 seconds to return the 84 rows to me that i'm expecting... Now,
> i've never experienced this kind of issue before so i've no idea what
> could be causing it. Some other queries are run before it and they finish
> fine (although a little slower than i would expect perhaps).I've also used
> MS Access to try and run the same view and I get the same problem as from
> code.
> Could there be any client settings that could cause this kind of
> difference?
> I'd appreciate anyones thoughts on this,
> Thanks,
> Andrew
>
|||Thanks Rick,
I've tried the profiler to make sure that my call was making it was far as
the SQL Server and I can see the query being started. However, after my
timeout period has elapsed, it just shows up in the profiler with the number
of reads etc and also showing the query time as being just more than the
timeout period. Initially I was wondering if some kind of lock was
preventing the query from finishing, however, I'm afraid I don't have access
to see that kind of information on my SQL Server, and it seemed unlikely if
other types of client were able to run the query (SQL Server management
studio)
Andrew
"Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
news:C47DC58D-8643-44AE-9397-8A7DD092F510@.microsoft.com...
> Try turning on SQL Server Profiler and see if SQL Server is really
> receiving the same query from both sources. You can also check the query
> execution command to double check the performance.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Andrew Brook" <ykoorb@.hotmail.com> wrote in message
> news:Ou7KiNcNIHA.4832@.TK2MSFTNGP04.phx.gbl...
>
|||In a freak revelation, it turns out I wasn't executing the same query I
thought I was executing (even after confirming with SQL Profiler...).
Unfortunately it turned out that my development DB and production DB were
very different, and I didn't have access to determine this earlier.
Apologies for any time spent thinking about this. I now have a much easier
task of optimizing a query that takes too long.
thanks,
Andrew
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:%23adOLIlNIHA.4196@.TK2MSFTNGP04.phx.gbl...
> Thanks Rick,
> I've tried the profiler to make sure that my call was making it was far as
> the SQL Server and I can see the query being started. However, after my
> timeout period has elapsed, it just shows up in the profiler with the
> number of reads etc and also showing the query time as being just more
> than the timeout period. Initially I was wondering if some kind of lock
> was preventing the query from finishing, however, I'm afraid I don't have
> access to see that kind of information on my SQL Server, and it seemed
> unlikely if other types of client were able to run the query (SQL Server
> management studio)
> Andrew
> "Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
> news:C47DC58D-8643-44AE-9397-8A7DD092F510@.microsoft.com...
>
|||No problems. It's the oldest issue in the book--debugging the wrong program.
Been there, done that, have the scars... ;)
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:u2XBYemNIHA.3400@.TK2MSFTNGP03.phx.gbl...
> In a freak revelation, it turns out I wasn't executing the same query I
> thought I was executing (even after confirming with SQL Profiler...).
> Unfortunately it turned out that my development DB and production DB were
> very different, and I didn't have access to determine this earlier.
> Apologies for any time spent thinking about this. I now have a much easier
> task of optimizing a query that takes too long.
> thanks,
> Andrew
>
> "Andrew Brook" <ykoorb@.hotmail.com> wrote in message
> news:%23adOLIlNIHA.4196@.TK2MSFTNGP04.phx.gbl...
>
first of all, i hope this is the correct newsgroup for this question
I've hit a scenario where I'm execuing a view from inside some .NET code.
I've set a timeout of 10 minutes, although this should be more than enough.
However, the query times out. When i connect to the database using SQL
Server Management Studio (full version) and execute the same view it takes 4
seconds to return the 84 rows to me that i'm expecting... Now, i've never
experienced this kind of issue before so i've no idea what could be causing
it. Some other queries are run before it and they finish fine (although a
little slower than i would expect perhaps).I've also used MS Access to try
and run the same view and I get the same problem as from code.
Could there be any client settings that could cause this kind of difference?
I'd appreciate anyones thoughts on this,
Thanks,
Andrew
Try turning on SQL Server Profiler and see if SQL Server is really receiving
the same query from both sources. You can also check the query execution
command to double check the performance.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:Ou7KiNcNIHA.4832@.TK2MSFTNGP04.phx.gbl...
> Hi everyone,
> first of all, i hope this is the correct newsgroup for this question
> I've hit a scenario where I'm execuing a view from inside some .NET code.
> I've set a timeout of 10 minutes, although this should be more than
> enough. However, the query times out. When i connect to the database using
> SQL Server Management Studio (full version) and execute the same view it
> takes 4 seconds to return the 84 rows to me that i'm expecting... Now,
> i've never experienced this kind of issue before so i've no idea what
> could be causing it. Some other queries are run before it and they finish
> fine (although a little slower than i would expect perhaps).I've also used
> MS Access to try and run the same view and I get the same problem as from
> code.
> Could there be any client settings that could cause this kind of
> difference?
> I'd appreciate anyones thoughts on this,
> Thanks,
> Andrew
>
|||Thanks Rick,
I've tried the profiler to make sure that my call was making it was far as
the SQL Server and I can see the query being started. However, after my
timeout period has elapsed, it just shows up in the profiler with the number
of reads etc and also showing the query time as being just more than the
timeout period. Initially I was wondering if some kind of lock was
preventing the query from finishing, however, I'm afraid I don't have access
to see that kind of information on my SQL Server, and it seemed unlikely if
other types of client were able to run the query (SQL Server management
studio)
Andrew
"Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
news:C47DC58D-8643-44AE-9397-8A7DD092F510@.microsoft.com...
> Try turning on SQL Server Profiler and see if SQL Server is really
> receiving the same query from both sources. You can also check the query
> execution command to double check the performance.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Andrew Brook" <ykoorb@.hotmail.com> wrote in message
> news:Ou7KiNcNIHA.4832@.TK2MSFTNGP04.phx.gbl...
>
|||In a freak revelation, it turns out I wasn't executing the same query I
thought I was executing (even after confirming with SQL Profiler...).
Unfortunately it turned out that my development DB and production DB were
very different, and I didn't have access to determine this earlier.
Apologies for any time spent thinking about this. I now have a much easier
task of optimizing a query that takes too long.
thanks,
Andrew
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:%23adOLIlNIHA.4196@.TK2MSFTNGP04.phx.gbl...
> Thanks Rick,
> I've tried the profiler to make sure that my call was making it was far as
> the SQL Server and I can see the query being started. However, after my
> timeout period has elapsed, it just shows up in the profiler with the
> number of reads etc and also showing the query time as being just more
> than the timeout period. Initially I was wondering if some kind of lock
> was preventing the query from finishing, however, I'm afraid I don't have
> access to see that kind of information on my SQL Server, and it seemed
> unlikely if other types of client were able to run the query (SQL Server
> management studio)
> Andrew
> "Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
> news:C47DC58D-8643-44AE-9397-8A7DD092F510@.microsoft.com...
>
|||No problems. It's the oldest issue in the book--debugging the wrong program.
Been there, done that, have the scars... ;)
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:u2XBYemNIHA.3400@.TK2MSFTNGP03.phx.gbl...
> In a freak revelation, it turns out I wasn't executing the same query I
> thought I was executing (even after confirming with SQL Profiler...).
> Unfortunately it turned out that my development DB and production DB were
> very different, and I didn't have access to determine this earlier.
> Apologies for any time spent thinking about this. I now have a much easier
> task of optimizing a query that takes too long.
> thanks,
> Andrew
>
> "Andrew Brook" <ykoorb@.hotmail.com> wrote in message
> news:%23adOLIlNIHA.4196@.TK2MSFTNGP04.phx.gbl...
>
Query execution takes different times from different clients
Hi everyone,
first of all, i hope this is the correct newsgroup for this question
I've hit a scenario where I'm execuing a view from inside some .NET code.
I've set a timeout of 10 minutes, although this should be more than enough.
However, the query times out. When i connect to the database using SQL
Server Management Studio (full version) and execute the same view it takes 4
seconds to return the 84 rows to me that i'm expecting... Now, i've never
experienced this kind of issue before so i've no idea what could be causing
it. Some other queries are run before it and they finish fine (although a
little slower than i would expect perhaps).I've also used MS Access to try
and run the same view and I get the same problem as from code.
Could there be any client settings that could cause this kind of difference?
I'd appreciate anyones thoughts on this,
Thanks,
AndrewTry turning on SQL Server Profiler and see if SQL Server is really receiving
the same query from both sources. You can also check the query execution
command to double check the performance.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:Ou7KiNcNIHA.4832@.TK2MSFTNGP04.phx.gbl...
> Hi everyone,
> first of all, i hope this is the correct newsgroup for this question
> I've hit a scenario where I'm execuing a view from inside some .NET code.
> I've set a timeout of 10 minutes, although this should be more than
> enough. However, the query times out. When i connect to the database using
> SQL Server Management Studio (full version) and execute the same view it
> takes 4 seconds to return the 84 rows to me that i'm expecting... Now,
> i've never experienced this kind of issue before so i've no idea what
> could be causing it. Some other queries are run before it and they finish
> fine (although a little slower than i would expect perhaps).I've also used
> MS Access to try and run the same view and I get the same problem as from
> code.
> Could there be any client settings that could cause this kind of
> difference?
> I'd appreciate anyones thoughts on this,
> Thanks,
> Andrew
>|||Thanks Rick,
I've tried the profiler to make sure that my call was making it was far as
the SQL Server and I can see the query being started. However, after my
timeout period has elapsed, it just shows up in the profiler with the number
of reads etc and also showing the query time as being just more than the
timeout period. Initially I was wondering if some kind of lock was
preventing the query from finishing, however, I'm afraid I don't have access
to see that kind of information on my SQL Server, and it seemed unlikely if
other types of client were able to run the query (SQL Server management
studio)
Andrew
"Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
news:C47DC58D-8643-44AE-9397-8A7DD092F510@.microsoft.com...
> Try turning on SQL Server Profiler and see if SQL Server is really
> receiving the same query from both sources. You can also check the query
> execution command to double check the performance.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Andrew Brook" <ykoorb@.hotmail.com> wrote in message
> news:Ou7KiNcNIHA.4832@.TK2MSFTNGP04.phx.gbl...
>|||In a freak revelation, it turns out I wasn't executing the same query I
thought I was executing (even after confirming with SQL Profiler...).
Unfortunately it turned out that my development DB and production DB were
very different, and I didn't have access to determine this earlier.
Apologies for any time spent thinking about this. I now have a much easier
task of optimizing a query that takes too long.
thanks,
Andrew
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:%23adOLIlNIHA.4196@.TK2MSFTNGP04.phx.gbl...
> Thanks Rick,
> I've tried the profiler to make sure that my call was making it was far as
> the SQL Server and I can see the query being started. However, after my
> timeout period has elapsed, it just shows up in the profiler with the
> number of reads etc and also showing the query time as being just more
> than the timeout period. Initially I was wondering if some kind of lock
> was preventing the query from finishing, however, I'm afraid I don't have
> access to see that kind of information on my SQL Server, and it seemed
> unlikely if other types of client were able to run the query (SQL Server
> management studio)
> Andrew
> "Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
> news:C47DC58D-8643-44AE-9397-8A7DD092F510@.microsoft.com...
>|||No problems. It's the oldest issue in the book--debugging the wrong program.
Been there, done that, have the scars... ;)
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:u2XBYemNIHA.3400@.TK2MSFTNGP03.phx.gbl...
> In a freak revelation, it turns out I wasn't executing the same query I
> thought I was executing (even after confirming with SQL Profiler...).
> Unfortunately it turned out that my development DB and production DB were
> very different, and I didn't have access to determine this earlier.
> Apologies for any time spent thinking about this. I now have a much easier
> task of optimizing a query that takes too long.
> thanks,
> Andrew
>
> "Andrew Brook" <ykoorb@.hotmail.com> wrote in message
> news:%23adOLIlNIHA.4196@.TK2MSFTNGP04.phx.gbl...
>sql
first of all, i hope this is the correct newsgroup for this question
I've hit a scenario where I'm execuing a view from inside some .NET code.
I've set a timeout of 10 minutes, although this should be more than enough.
However, the query times out. When i connect to the database using SQL
Server Management Studio (full version) and execute the same view it takes 4
seconds to return the 84 rows to me that i'm expecting... Now, i've never
experienced this kind of issue before so i've no idea what could be causing
it. Some other queries are run before it and they finish fine (although a
little slower than i would expect perhaps).I've also used MS Access to try
and run the same view and I get the same problem as from code.
Could there be any client settings that could cause this kind of difference?
I'd appreciate anyones thoughts on this,
Thanks,
AndrewTry turning on SQL Server Profiler and see if SQL Server is really receiving
the same query from both sources. You can also check the query execution
command to double check the performance.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:Ou7KiNcNIHA.4832@.TK2MSFTNGP04.phx.gbl...
> Hi everyone,
> first of all, i hope this is the correct newsgroup for this question
> I've hit a scenario where I'm execuing a view from inside some .NET code.
> I've set a timeout of 10 minutes, although this should be more than
> enough. However, the query times out. When i connect to the database using
> SQL Server Management Studio (full version) and execute the same view it
> takes 4 seconds to return the 84 rows to me that i'm expecting... Now,
> i've never experienced this kind of issue before so i've no idea what
> could be causing it. Some other queries are run before it and they finish
> fine (although a little slower than i would expect perhaps).I've also used
> MS Access to try and run the same view and I get the same problem as from
> code.
> Could there be any client settings that could cause this kind of
> difference?
> I'd appreciate anyones thoughts on this,
> Thanks,
> Andrew
>|||Thanks Rick,
I've tried the profiler to make sure that my call was making it was far as
the SQL Server and I can see the query being started. However, after my
timeout period has elapsed, it just shows up in the profiler with the number
of reads etc and also showing the query time as being just more than the
timeout period. Initially I was wondering if some kind of lock was
preventing the query from finishing, however, I'm afraid I don't have access
to see that kind of information on my SQL Server, and it seemed unlikely if
other types of client were able to run the query (SQL Server management
studio)
Andrew
"Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
news:C47DC58D-8643-44AE-9397-8A7DD092F510@.microsoft.com...
> Try turning on SQL Server Profiler and see if SQL Server is really
> receiving the same query from both sources. You can also check the query
> execution command to double check the performance.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Andrew Brook" <ykoorb@.hotmail.com> wrote in message
> news:Ou7KiNcNIHA.4832@.TK2MSFTNGP04.phx.gbl...
>|||In a freak revelation, it turns out I wasn't executing the same query I
thought I was executing (even after confirming with SQL Profiler...).
Unfortunately it turned out that my development DB and production DB were
very different, and I didn't have access to determine this earlier.
Apologies for any time spent thinking about this. I now have a much easier
task of optimizing a query that takes too long.
thanks,
Andrew
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:%23adOLIlNIHA.4196@.TK2MSFTNGP04.phx.gbl...
> Thanks Rick,
> I've tried the profiler to make sure that my call was making it was far as
> the SQL Server and I can see the query being started. However, after my
> timeout period has elapsed, it just shows up in the profiler with the
> number of reads etc and also showing the query time as being just more
> than the timeout period. Initially I was wondering if some kind of lock
> was preventing the query from finishing, however, I'm afraid I don't have
> access to see that kind of information on my SQL Server, and it seemed
> unlikely if other types of client were able to run the query (SQL Server
> management studio)
> Andrew
> "Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
> news:C47DC58D-8643-44AE-9397-8A7DD092F510@.microsoft.com...
>|||No problems. It's the oldest issue in the book--debugging the wrong program.
Been there, done that, have the scars... ;)
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:u2XBYemNIHA.3400@.TK2MSFTNGP03.phx.gbl...
> In a freak revelation, it turns out I wasn't executing the same query I
> thought I was executing (even after confirming with SQL Profiler...).
> Unfortunately it turned out that my development DB and production DB were
> very different, and I didn't have access to determine this earlier.
> Apologies for any time spent thinking about this. I now have a much easier
> task of optimizing a query that takes too long.
> thanks,
> Andrew
>
> "Andrew Brook" <ykoorb@.hotmail.com> wrote in message
> news:%23adOLIlNIHA.4196@.TK2MSFTNGP04.phx.gbl...
>sql
Tuesday, March 20, 2012
Query execution failed for data set <name> For more information about this error navigate
I have created and deployed my first report. It renders fine for me and the other database admin. When others attempt to view it, we get the error
Query execution failed for data set 'periods'. (rsErrorExecutingCommand), For more information about this error navigate to the report server on the local server machine, or enable remote errors
Initially, We created a local group on the machine that hosts both the database and webserver and added the individuals to that group. Then, within SRS Report manager, we added that group to the Browswer role of the report.
The error message was slightly different, in that it couldn't even open the Datasource.
We then added an individual to the database as dbreader, and got the above message. It apprently is starting to render, and when it encounters the first query (dataset "periods", which populates a drop down list for a parameter), it chokes. BTW, the Periods dataset executes a stored procedure dbo.Period_List that has no parameters. It returns a list of reporting periods.
I could not figure out how to "enable remote errors" or find an error log on the server. The C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles Log files did not appear to record any errors.
Please advise!
Have you given execute permissions on the stored procedure? You can try troubleshooting with SQL Server Profiler (http://msdn2.microsoft.com/en-us/library/ms187929.aspx), but that might be like hitting a gnat with a sledge hammer.
Larry
|||I want to add some information. We recently enabled remote errors, and found that EXECUTE permission on the stored procedure was denied.
Does this mean that we have to grant permissions on every stored procedure to User Groups?
I love SRS, but this is turning into an Admin nightmare.
|||Ok, let's back up a second and take another run at this. As long as you don't require the data on your reports to change based on who is viewing them, use a shared DataSource with stored credentials (http://technet.microsoft.com/en-us/library/ms178308.aspx). That way, you admin a single user and everyone (including you during development) sees the same report.
To continue to use Windows Authentication, yes, you would need to grant permissions for every stored procedure that you want SSRS to use to any user that will run a report using that stored procedure.
Larry
|||thank you!
Ok, the first option sounds like the way to go for the moment, but something you said intrigued me...
"As long as you don't require the data on your reports to change based on who is viewing them..."
Actually, we would like to restrict what the users see based upon who they are, and haven't figured out how to do that yet. For example, if a person is from business unit A, we would like them only to see their business units data. All business units are in the same table however, so we would have to use a parameter or a filter, based upon the longin User ID. Not sure how to do this, any ideas?
|||Well, you could implement row level security to filter the results returned based on the user. Here is a tutorial from Books On Line (BOL) (http://msdn2.microsoft.com/en-us/library/ms365305.aspx) and here is a white paper discussing it (http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx). It is a bit complex to get setup, but the results are well worth the effort.
Larry
Query execution failed
Hi,
If there's a problem with it, I don't get why it works in preview mode. I'm using SQL server 2005.
|||After talking to MS support, it seems that if you use a column name alias (ie "as") in the select statement, the report viewer refuses to parse the SQL. It seems to be some sort of bug. We were able to get around it by having the report run a stored procedure, which allowed the column names. Hope this helps anyone else that runs into this issue.
I have a group of reports using a shared datasource. Going to the preview of the report works fine in the report designer, but when I try and view it from a browser (deployed on a website), it gives the error:
"An error has occurred during report processing.
Query execution failed for data set 'DataSet1_ticketInfo'.
Failed to parse SQL.[long sql query here]"
If there's a problem with it, I don't get why it works in preview mode. I'm using SQL server 2005.
Thanks.
Hi,
Can you provide some more details about your Report
1) 'DataSet1_ticketInfo' - Is this your main DataSet ie; the result of this DS is used on the Report body OR
2) Are you using 'DataSet1_ticketInfo' DS for populating Report Parameters OR
3) Your DS contains some script (query or Stored Proc) that requires special permissions to be executed through your asp.net web-site
I think you may be missing some params used by above mentioned DS, which may be causing the error.
Regards,
abhi_viking
|||That dataset is the main dataset for the report. One of these reports (all are having the same problem) does take parameters, but I have a default value set, which actually shows up in the datepicker correctly. This particular dataset doesn't take any parameters. The actual report doesn't load however (but does in preview)
|||
Hi,
Are you using some code that requires permission to be executed thro' your asp.net website?
If possible, do paste ur DS code here, that might help us solve your issue.
Regards,
abhi_viking
|||How would I get the actual code of the DS? Just to be clear, this is a server report, not a client one. I've verified that the conn. string is correct and does connect ok.|||After talking to MS support, it seems that if you use a column name alias (ie "as") in the select statement, the report viewer refuses to parse the SQL. It seems to be some sort of bug. We were able to get around it by having the report run a stored procedure, which allowed the column names. Hope this helps anyone else that runs into this issue.
Query execution failed
Hi,
If there's a problem with it, I don't get why it works in preview mode. I'm using SQL server 2005.
|||After talking to MS support, it seems that if you use a column name alias (ie "as") in the select statement, the report viewer refuses to parse the SQL. It seems to be some sort of bug. We were able to get around it by having the report run a stored procedure, which allowed the column names. Hope this helps anyone else that runs into this issue.
I have a group of reports using a shared datasource. Going to the preview of the report works fine in the report designer, but when I try and view it from a browser (deployed on a website), it gives the error:
"An error has occurred during report processing.
Query execution failed for data set 'DataSet1_ticketInfo'.
Failed to parse SQL.[long sql query here]"
If there's a problem with it, I don't get why it works in preview mode. I'm using SQL server 2005.
Thanks.
Hi,
Can you provide some more details about your Report
1) 'DataSet1_ticketInfo' - Is this your main DataSet ie; the result of this DS is used on the Report body OR
2) Are you using 'DataSet1_ticketInfo' DS for populating Report Parameters OR
3) Your DS contains some script (query or Stored Proc) that requires special permissions to be executed through your asp.net web-site
I think you may be missing some params used by above mentioned DS, which may be causing the error.
Regards,
abhi_viking
|||That dataset is the main dataset for the report. One of these reports (all are having the same problem) does take parameters, but I have a default value set, which actually shows up in the datepicker correctly. This particular dataset doesn't take any parameters. The actual report doesn't load however (but does in preview)
|||
Hi,
Are you using some code that requires permission to be executed thro' your asp.net website?
If possible, do paste ur DS code here, that might help us solve your issue.
Regards,
abhi_viking
|||How would I get the actual code of the DS? Just to be clear, this is a server report, not a client one. I've verified that the conn. string is correct and does connect ok.|||After talking to MS support, it seems that if you use a column name alias (ie "as") in the select statement, the report viewer refuses to parse the SQL. It seems to be some sort of bug. We were able to get around it by having the report run a stored procedure, which allowed the column names. Hope this helps anyone else that runs into this issue.
Query execution failed
Hi,
If there's a problem with it, I don't get why it works in preview mode. I'm using SQL server 2005.
|||After talking to MS support, it seems that if you use a column name alias (ie "as") in the select statement, the report viewer refuses to parse the SQL. It seems to be some sort of bug. We were able to get around it by having the report run a stored procedure, which allowed the column names. Hope this helps anyone else that runs into this issue.
I have a group of reports using a shared datasource. Going to the preview of the report works fine in the report designer, but when I try and view it from a browser (deployed on a website), it gives the error:
"An error has occurred during report processing.
Query execution failed for data set 'DataSet1_ticketInfo'.
Failed to parse SQL.[long sql query here]"
If there's a problem with it, I don't get why it works in preview mode. I'm using SQL server 2005.
Thanks.
Hi,
Can you provide some more details about your Report
1) 'DataSet1_ticketInfo' - Is this your main DataSet ie; the result of this DS is used on the Report body OR
2) Are you using 'DataSet1_ticketInfo' DS for populating Report Parameters OR
3) Your DS contains some script (query or Stored Proc) that requires special permissions to be executed through your asp.net web-site
I think you may be missing some params used by above mentioned DS, which may be causing the error.
Regards,
abhi_viking
|||That dataset is the main dataset for the report. One of these reports (all are having the same problem) does take parameters, but I have a default value set, which actually shows up in the datepicker correctly. This particular dataset doesn't take any parameters. The actual report doesn't load however (but does in preview)
|||
Hi,
Are you using some code that requires permission to be executed thro' your asp.net website?
If possible, do paste ur DS code here, that might help us solve your issue.
Regards,
abhi_viking
|||How would I get the actual code of the DS? Just to be clear, this is a server report, not a client one. I've verified that the conn. string is correct and does connect ok.|||After talking to MS support, it seems that if you use a column name alias (ie "as") in the select statement, the report viewer refuses to parse the SQL. It seems to be some sort of bug. We were able to get around it by having the report run a stored procedure, which allowed the column names. Hope this helps anyone else that runs into this issue.
Monday, March 12, 2012
query engine error 21000
Can anyone tell me why I am getting this error when I try to view my report:
Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][SQL Sever] Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=,>,>= or when the subquery is used as an
Hi there...looks like one of the queries being executed to retrieve data for the report contains an invalid use of a subquery that returns more than 1 record in a scalar context. For example, something like what follows below is illegel if tableb contains more than one row, since the subquery is being used in/expected to act in scalar context (i.e. a single column, single row result set):
select cola from tablea where colb = (select colb from tableb)
If you have the query, maybe we could help some more...
query engine error
Can anyone tell me why I am getting this error when I try to view my report:
Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][SQL Sever] Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=,>,>= or when the subquery is used as anThis is a problem in the SQL-query.
Like the error says, the query has a subquery that returns multiple values (more than one row and column). Subqueries like that can't be in the column list or after the listed operators.
Make sure that the subquery returns only one value.
For example, next query won't work, as the subquery would return many rows (assuming there are many rows in the Suppliers-table):
SELECT ProductID, (SELECT SupplierID FROM Suppliers)
FROM Products
Similarly, the next query will cause the same error:
SELECT ProductID
FROM Products
WHERE SupplierID = (SELECT SupplierID FROM Suppliers)
The solution is to make the subquery to return only one value using WHERE or ORDER BY + TOP 1 statements, for example.|||
Sometime this might fit your need to limit the returned value to only a single on. Sometime you might WANt to return more than one value, then you will either have to use a JOIN, correlated query, or the IN operator like
Similarly, the next query will cause the same error:
SELECT ProductID
FROM Products
WHERE SupplierID IN (SELECT SupplierID FROM Suppliers)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Query engine error
Can anyone tell me why I am getting this error when I try to view my report:
Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][SQL Sever]
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <=,>,>= or when the subquery is used as an expression.
thanks in advance!
It would help to see your query.
But I expect that it is something like:
Select (select col from tbl where key = topkey) as subcol
from toptable
If the subquery could return more than one value, the query will error out.
Russel Loski, MCSD.Net
"hale" wrote:
> Can anyone tell me why I am getting this error when I try to view my report:
> Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][SQL Sever]
> Subquery returned more than 1 value. This is not permitted when the subquery
> follows =, !=, <, <=,>,>= or when the subquery is used as an expression.
>
> --
> thanks in advance!
>
Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][SQL Sever]
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <=,>,>= or when the subquery is used as an expression.
thanks in advance!
It would help to see your query.
But I expect that it is something like:
Select (select col from tbl where key = topkey) as subcol
from toptable
If the subquery could return more than one value, the query will error out.
Russel Loski, MCSD.Net
"hale" wrote:
> Can anyone tell me why I am getting this error when I try to view my report:
> Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][SQL Sever]
> Subquery returned more than 1 value. This is not permitted when the subquery
> follows =, !=, <, <=,>,>= or when the subquery is used as an expression.
>
> --
> thanks in advance!
>
Query engine error
Can anyone tell me why I am getting this error when I try to view my report:
Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][S
QL Sever]
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <=,>,>= or when the subquery is used as an expression.
thanks in advance!It would help to see your query.
But I expect that it is something like:
Select (select col from tbl where key = topkey) as subcol
from toptable
If the subquery could return more than one value, the query will error out.
Russel Loski, MCSD.Net
"hale" wrote:
> Can anyone tell me why I am getting this error when I try to view my repor
t:
> Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][
;SQL Sever]
> Subquery returned more than 1 value. This is not permitted when the subque
ry
> follows =, !=, <, <=,>,>= or when the subquery is used as an expression.
>
> --
> thanks in advance!
>
Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][S
QL Sever]
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <=,>,>= or when the subquery is used as an expression.
thanks in advance!It would help to see your query.
But I expect that it is something like:
Select (select col from tbl where key = topkey) as subcol
from toptable
If the subquery could return more than one value, the query will error out.
Russel Loski, MCSD.Net
"hale" wrote:
> Can anyone tell me why I am getting this error when I try to view my repor
t:
> Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][
;SQL Sever]
> Subquery returned more than 1 value. This is not permitted when the subque
ry
> follows =, !=, <, <=,>,>= or when the subquery is used as an expression.
>
> --
> thanks in advance!
>
query does oposite of what i want
I am trying to find all the user tables that are not mentioned in a view or
stored procedure. Instead, this lists each table once for each stored
procedure it is not appearing.
select so2.name,so.name
from AdminDB.dbo.sysobjects so inner join AdminDB.dbo.syscomments sc on
(so.id=sc.id)
inner join AdminDB.dbo.sysobjects so2 on
(patindex('%so2.name%',sc.text)=0)
where so.xtype in ('P','V')
and so2.xtype ='U'
order by so2.name
GOT IT!
SELECT a.name
FROM AdminDB.dbo.sysobjects a LEFT JOIN (
SELECT so2.name,so.name AS 'usedin'
FROM AdminDB.dbo.sysobjects so INNER JOIN AdminDB.dbo.syscomments sc
ON (so.id=sc.id)
INNER JOIN AdminDB.dbo.sysobjects so2 ON (patindex('%' + so2.name+
'%',sc.text)>0)
WHERE so.xtype IN ('P','V','FN')
AND so2.xtype ='U'
) x ON a.name = x.name
WHERE x.name IS NULL
AND a.xtype IN ('U')
"DBA72" wrote:
> I am trying to find all the user tables that are not mentioned in a view or
> stored procedure. Instead, this lists each table once for each stored
> procedure it is not appearing.
> select so2.name,so.name
> from AdminDB.dbo.sysobjects so inner join AdminDB.dbo.syscomments sc on
> (so.id=sc.id)
> inner join AdminDB.dbo.sysobjects so2 on
> (patindex('%so2.name%',sc.text)=0)
> where so.xtype in ('P','V')
> and so2.xtype ='U'
> order by so2.name
|||I would use the ANSI schema views, as opposed to directly accessing system
tables. Does this yield the same result as your query?
SELECT t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT OUTER JOIN
(
SELECT
oname = ROUTINE_NAME,
odef = ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
UNION ALL
SELECT
oname = TABLE_NAME,
odef = VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
) o
ON o.odef LIKE '%'+t.TABLE_NAME+'%'
WHERE o.oname IS NULL
Note, of course, that pattern matching isn't perfect, for example there are
these (and probably many other) limitations:
(a) a table name could be mentioned in a comment (false positive)
(b) a table name could be spread across multiple rows for a proc/view>8000
characters (missing)
(c) the view/proc name could be the same as or contain the table name, but
not actually depend on it
http://www.aspfaq.com/
(Reverse address to reply.)
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:BB5D2A28-C9A7-4606-82A6-3E7960E72065@.microsoft.com...[vbcol=seagreen]
> GOT IT!
> SELECT a.name
> FROM AdminDB.dbo.sysobjects a LEFT JOIN (
> SELECT so2.name,so.name AS 'usedin'
> FROM AdminDB.dbo.sysobjects so INNER JOIN AdminDB.dbo.syscomments sc
> ON (so.id=sc.id)
> INNER JOIN AdminDB.dbo.sysobjects so2 ON (patindex('%' + so2.name+
> '%',sc.text)>0)
> WHERE so.xtype IN ('P','V','FN')
> AND so2.xtype ='U'
> ) x ON a.name = x.name
> WHERE x.name IS NULL
> AND a.xtype IN ('U')
> "DBA72" wrote:
or[vbcol=seagreen]
stored procedure. Instead, this lists each table once for each stored
procedure it is not appearing.
select so2.name,so.name
from AdminDB.dbo.sysobjects so inner join AdminDB.dbo.syscomments sc on
(so.id=sc.id)
inner join AdminDB.dbo.sysobjects so2 on
(patindex('%so2.name%',sc.text)=0)
where so.xtype in ('P','V')
and so2.xtype ='U'
order by so2.name
GOT IT!
SELECT a.name
FROM AdminDB.dbo.sysobjects a LEFT JOIN (
SELECT so2.name,so.name AS 'usedin'
FROM AdminDB.dbo.sysobjects so INNER JOIN AdminDB.dbo.syscomments sc
ON (so.id=sc.id)
INNER JOIN AdminDB.dbo.sysobjects so2 ON (patindex('%' + so2.name+
'%',sc.text)>0)
WHERE so.xtype IN ('P','V','FN')
AND so2.xtype ='U'
) x ON a.name = x.name
WHERE x.name IS NULL
AND a.xtype IN ('U')
"DBA72" wrote:
> I am trying to find all the user tables that are not mentioned in a view or
> stored procedure. Instead, this lists each table once for each stored
> procedure it is not appearing.
> select so2.name,so.name
> from AdminDB.dbo.sysobjects so inner join AdminDB.dbo.syscomments sc on
> (so.id=sc.id)
> inner join AdminDB.dbo.sysobjects so2 on
> (patindex('%so2.name%',sc.text)=0)
> where so.xtype in ('P','V')
> and so2.xtype ='U'
> order by so2.name
|||I would use the ANSI schema views, as opposed to directly accessing system
tables. Does this yield the same result as your query?
SELECT t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT OUTER JOIN
(
SELECT
oname = ROUTINE_NAME,
odef = ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
UNION ALL
SELECT
oname = TABLE_NAME,
odef = VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
) o
ON o.odef LIKE '%'+t.TABLE_NAME+'%'
WHERE o.oname IS NULL
Note, of course, that pattern matching isn't perfect, for example there are
these (and probably many other) limitations:
(a) a table name could be mentioned in a comment (false positive)
(b) a table name could be spread across multiple rows for a proc/view>8000
characters (missing)
(c) the view/proc name could be the same as or contain the table name, but
not actually depend on it
http://www.aspfaq.com/
(Reverse address to reply.)
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:BB5D2A28-C9A7-4606-82A6-3E7960E72065@.microsoft.com...[vbcol=seagreen]
> GOT IT!
> SELECT a.name
> FROM AdminDB.dbo.sysobjects a LEFT JOIN (
> SELECT so2.name,so.name AS 'usedin'
> FROM AdminDB.dbo.sysobjects so INNER JOIN AdminDB.dbo.syscomments sc
> ON (so.id=sc.id)
> INNER JOIN AdminDB.dbo.sysobjects so2 ON (patindex('%' + so2.name+
> '%',sc.text)>0)
> WHERE so.xtype IN ('P','V','FN')
> AND so2.xtype ='U'
> ) x ON a.name = x.name
> WHERE x.name IS NULL
> AND a.xtype IN ('U')
> "DBA72" wrote:
or[vbcol=seagreen]
query does oposite of what i want
I am trying to find all the user tables that are not mentioned in a view or
stored procedure. Instead, this lists each table once for each stored
procedure it is not appearing.
select so2.name,so.name
from AdminDB.dbo.sysobjects so inner join AdminDB.dbo.syscomments sc on
(so.id=sc.id)
inner join AdminDB.dbo.sysobjects so2 on
(patindex('%so2.name%',sc.text)=0)
where so.xtype in ('P','V')
and so2.xtype ='U'
order by so2.nameGOT IT!
SELECT a.name
FROM AdminDB.dbo.sysobjects a LEFT JOIN (
SELECT so2.name,so.name AS 'usedin'
FROM AdminDB.dbo.sysobjects so INNER JOIN AdminDB.dbo.syscomments sc
ON (so.id=sc.id)
INNER JOIN AdminDB.dbo.sysobjects so2 ON (patindex('%' + so2.name+
'%',sc.text)>0)
WHERE so.xtype IN ('P','V','FN')
AND so2.xtype ='U'
) x ON a.name = x.name
WHERE x.name IS NULL
AND a.xtype IN ('U')
"DBA72" wrote:
> I am trying to find all the user tables that are not mentioned in a view o
r
> stored procedure. Instead, this lists each table once for each stored
> procedure it is not appearing.
> select so2.name,so.name
> from AdminDB.dbo.sysobjects so inner join AdminDB.dbo.syscomments sc on
> (so.id=sc.id)
> inner join AdminDB.dbo.sysobjects so2 on
> (patindex('%so2.name%',sc.text)=0)
> where so.xtype in ('P','V')
> and so2.xtype ='U'
> order by so2.name|||I would use the ANSI schema views, as opposed to directly accessing system
tables. Does this yield the same result as your query?
SELECT t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT OUTER JOIN
(
SELECT
oname = ROUTINE_NAME,
odef = ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
UNION ALL
SELECT
oname = TABLE_NAME,
odef = VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
) o
ON o.odef LIKE '%'+t.TABLE_NAME+'%'
WHERE o.oname IS NULL
Note, of course, that pattern matching isn't perfect, for example there are
these (and probably many other) limitations:
(a) a table name could be mentioned in a comment (false positive)
(b) a table name could be spread across multiple rows for a proc/view>8000
characters (missing)
(c) the view/proc name could be the same as or contain the table name, but
not actually depend on it
http://www.aspfaq.com/
(Reverse address to reply.)
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:BB5D2A28-C9A7-4606-82A6-3E7960E72065@.microsoft.com...[vbcol=seagreen]
> GOT IT!
> SELECT a.name
> FROM AdminDB.dbo.sysobjects a LEFT JOIN (
> SELECT so2.name,so.name AS 'usedin'
> FROM AdminDB.dbo.sysobjects so INNER JOIN AdminDB.dbo.syscomments sc
> ON (so.id=sc.id)
> INNER JOIN AdminDB.dbo.sysobjects so2 ON (patindex('%' + so2.name+
> '%',sc.text)>0)
> WHERE so.xtype IN ('P','V','FN')
> AND so2.xtype ='U'
> ) x ON a.name = x.name
> WHERE x.name IS NULL
> AND a.xtype IN ('U')
> "DBA72" wrote:
>
or[vbcol=seagreen]
stored procedure. Instead, this lists each table once for each stored
procedure it is not appearing.
select so2.name,so.name
from AdminDB.dbo.sysobjects so inner join AdminDB.dbo.syscomments sc on
(so.id=sc.id)
inner join AdminDB.dbo.sysobjects so2 on
(patindex('%so2.name%',sc.text)=0)
where so.xtype in ('P','V')
and so2.xtype ='U'
order by so2.nameGOT IT!
SELECT a.name
FROM AdminDB.dbo.sysobjects a LEFT JOIN (
SELECT so2.name,so.name AS 'usedin'
FROM AdminDB.dbo.sysobjects so INNER JOIN AdminDB.dbo.syscomments sc
ON (so.id=sc.id)
INNER JOIN AdminDB.dbo.sysobjects so2 ON (patindex('%' + so2.name+
'%',sc.text)>0)
WHERE so.xtype IN ('P','V','FN')
AND so2.xtype ='U'
) x ON a.name = x.name
WHERE x.name IS NULL
AND a.xtype IN ('U')
"DBA72" wrote:
> I am trying to find all the user tables that are not mentioned in a view o
r
> stored procedure. Instead, this lists each table once for each stored
> procedure it is not appearing.
> select so2.name,so.name
> from AdminDB.dbo.sysobjects so inner join AdminDB.dbo.syscomments sc on
> (so.id=sc.id)
> inner join AdminDB.dbo.sysobjects so2 on
> (patindex('%so2.name%',sc.text)=0)
> where so.xtype in ('P','V')
> and so2.xtype ='U'
> order by so2.name|||I would use the ANSI schema views, as opposed to directly accessing system
tables. Does this yield the same result as your query?
SELECT t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT OUTER JOIN
(
SELECT
oname = ROUTINE_NAME,
odef = ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
UNION ALL
SELECT
oname = TABLE_NAME,
odef = VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
) o
ON o.odef LIKE '%'+t.TABLE_NAME+'%'
WHERE o.oname IS NULL
Note, of course, that pattern matching isn't perfect, for example there are
these (and probably many other) limitations:
(a) a table name could be mentioned in a comment (false positive)
(b) a table name could be spread across multiple rows for a proc/view>8000
characters (missing)
(c) the view/proc name could be the same as or contain the table name, but
not actually depend on it
http://www.aspfaq.com/
(Reverse address to reply.)
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:BB5D2A28-C9A7-4606-82A6-3E7960E72065@.microsoft.com...[vbcol=seagreen]
> GOT IT!
> SELECT a.name
> FROM AdminDB.dbo.sysobjects a LEFT JOIN (
> SELECT so2.name,so.name AS 'usedin'
> FROM AdminDB.dbo.sysobjects so INNER JOIN AdminDB.dbo.syscomments sc
> ON (so.id=sc.id)
> INNER JOIN AdminDB.dbo.sysobjects so2 ON (patindex('%' + so2.name+
> '%',sc.text)>0)
> WHERE so.xtype IN ('P','V','FN')
> AND so2.xtype ='U'
> ) x ON a.name = x.name
> WHERE x.name IS NULL
> AND a.xtype IN ('U')
> "DBA72" wrote:
>
or[vbcol=seagreen]
Subscribe to:
Posts (Atom)