Thursday, March 29, 2012
easy way to list tables & columns?
Is there an easy process to list all the tables and columns of a particular
database? I just got this task and I wouldn't know where to start just yet.
What I'd like to see in my newly inherited database servers is a way to
quickly generate a list of a database's tables and columns within those
tables.
Is that too basic? I wouldn't mind seeing step-by-step, if somebody decides
to answer this.
Thanks very much,
BobStep-by-step comments are inline...
USE <your_database_name> /* this is the database for which you want to
see tables and columns */
SELECT sysobjects.name AS tablename /* our tables are listed in the
sysobjects system table (see where clause for filter)*/
, syscolumns.name AS columnname /* our columns are listed in
the syscolumns system table */
FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id /* we use the table id to
identify which columns belong to the table */
WHERE Objectproperty(sysobjects.id,N'IsUserTable') = 1 /* this will
list all the user tables and leave out system tables, stored
procedures, views, etc */
ORDER BY sysobjects.name /* list the tables in alphabetical order */
, syscolumns.name /* list the columns in alphabetical order
within their table */|||Bob wrote:
> I have a newbie question about MS SQL EM:
> Is there an easy process to list all the tables and columns of a
> particular database? I just got this task and I wouldn't know where
> to start just yet.
You can query the INFORMATION_SCHEMA.COLUMNS table for this information.
You can also query the system tables directly in each database:
sysobjects (type = 'U') and syscolumns for the column information.
Some sample ADO code to do this:
http://www.avdf.com/aug98/art_vb006.html
David Gugick - SQL Server MVP
Quest Software
easy way to list tables & columns?
Is there an easy process to list all the tables and columns of a particular
database? I just got this task and I wouldn't know where to start just yet.
What I'd like to see in my newly inherited database servers is a way to
quickly generate a list of a database's tables and columns within those
tables.
Is that too basic? I wouldn't mind seeing step-by-step, if somebody decides
to answer this.
Thanks very much,
Bob
Step-by-step comments are inline...
USE <your_database_name> /* this is the database for which you want to
see tables and columns */
SELECT sysobjects.name AS tablename /* our tables are listed in the
sysobjects system table (see where clause for filter)*/
, syscolumns.name AS columnname /* our columns are listed in
the syscolumns system table */
FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id /* we use the table id to
identify which columns belong to the table */
WHERE Objectproperty(sysobjects.id,N'IsUserTable') = 1 /* this will
list all the user tables and leave out system tables, stored
procedures, views, etc */
ORDER BY sysobjects.name /* list the tables in alphabetical order */
, syscolumns.name /* list the columns in alphabetical order
within their table */
|||Bob wrote:
> I have a newbie question about MS SQL EM:
> Is there an easy process to list all the tables and columns of a
> particular database? I just got this task and I wouldn't know where
> to start just yet.
You can query the INFORMATION_SCHEMA.COLUMNS table for this information.
You can also query the system tables directly in each database:
sysobjects (type = 'U') and syscolumns for the column information.
Some sample ADO code to do this:
http://www.avdf.com/aug98/art_vb006.html
David Gugick - SQL Server MVP
Quest Software
easy way to list tables & columns?
Is there an easy process to list all the tables and columns of a particular
database? I just got this task and I wouldn't know where to start just yet.
What I'd like to see in my newly inherited database servers is a way to
quickly generate a list of a database's tables and columns within those
tables.
Is that too basic? I wouldn't mind seeing step-by-step, if somebody decides
to answer this.
Thanks very much,
BobStep-by-step comments are inline...
USE <your_database_name> /* this is the database for which you want to
see tables and columns */
SELECT sysobjects.name AS tablename /* our tables are listed in the
sysobjects system table (see where clause for filter)*/
, syscolumns.name AS columnname /* our columns are listed in
the syscolumns system table */
FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id /* we use the table id to
identify which columns belong to the table */
WHERE Objectproperty(sysobjects.id,N'IsUserTable') = 1 /* this will
list all the user tables and leave out system tables, stored
procedures, views, etc */
ORDER BY sysobjects.name /* list the tables in alphabetical order */
, syscolumns.name /* list the columns in alphabetical order
within their table */|||Bob wrote:
> I have a newbie question about MS SQL EM:
> Is there an easy process to list all the tables and columns of a
> particular database? I just got this task and I wouldn't know where
> to start just yet.
You can query the INFORMATION_SCHEMA.COLUMNS table for this information.
You can also query the system tables directly in each database:
sysobjects (type = 'U') and syscolumns for the column information.
Some sample ADO code to do this:
http://www.avdf.com/aug98/art_vb006.html
David Gugick - SQL Server MVP
Quest Software
Tuesday, March 27, 2012
easy sql help please
dbo.tbl_client
field names are q1_1, q1_2, q1_3, q1_4
where relat_to='manager
i need to find the average for these
i tried:
select sum(q1_1+q1_2+q1_3+q1_4/4) as average_q1
FROM dbo.tbl_client
WHERE relat_to='manager'
gave me a strange number!
also... how can i find the MAX for these answers? across multiple columns?
select MAX(??what goes here?) as q1_max
from dbo.tbl_client
where relat_to='manager'
thank you!!!!
michaelClassic order of operations, Michael. Division comes before addition, so your code is operating like this:
sum(q1_1+q1_2+q1_3+(q1_4/4))
You want this:
sum(q1_1+q1_2+q1_3+q1_4)/4
or maybe this
sum((q1_1+q1_2+q1_3+q1_4)/4)
(They may be equivalent. My brain is too fried to think about it this late in the day!)
You may get strange results if some of your columns contain nulls, or if your columns are integer values.
Not sure what you want by MAX. MAX value? MAX column value? Max average?
blindman|||um... okay... i REALLY did pass math... honest!
i was thinking that with all the damn time i put in front of this computer monitor that it would read my thoughts and KNOW what i wanted...
thanks for the syntax!
as to the Max value... i had multiple questions Q1_1, Q1_2, Q1_3,Q1_4
the answers for each can range from 0 to 7
the WHERE Clause is relat_to='manager' (next SQL will be relat_to='self' etc)
what i need is to know what the MAX score is for relat_to='manager' for all of the Q1 questions... so if a person is a 'manager' and their results for the questions were Q1_1=4, Q1_2=0,Q1_3=7, Q1_4=0 then the MAX should be "7" and (i'll swap out the MAX after and do the MIN too) the MIN will be "0"
please let me know if this makes sense and thank you for helping someone that you dont even know!!|||First create a new function:
CREATE function dbo.LARGEST (@.value1 sql_variant, @.value2 sql_variant)
returns sql_variant as
--blindman, 8/03
--Returns the largest of two values
BEGIN
if @.value1 < @.value2 set @.value1 = @.value2
return @.value1
END
Then use this select statement:
Select dbo.Largest(Q1_1, dbo.Largest(Q1_2, dbo.Largest(Q1_3,Q1_4)))
blindman|||i'm obviously way over my head here.... i have no idea whatsoever how to create a function... i'm using Dreamweaver and its recordset interface and the database was an Access that was upsized.... i am committed to finsihing it this week and just may end up giving all this computer stuff up to becme a rodeo clown....
i'm searching now for how to create a function etc... i really do appreciate all you help!!! i'm just sorry that i'm not smart enough to implement it!!!!
michael|||You can just run this code against your database using Query Analyzer:
CREATE function dbo.LARGEST (@.value1 sql_variant, @.value2 sql_variant)
returns sql_variant as
--blindman, 8/03
--Returns the largest of two values
BEGIN
if @.value1 < @.value2 set @.value1 = @.value2
return @.value1
END
That will create your function. You can call your function and pass parameters just like you would call any other function, except that you must specify the owner of the function, "dbo". The code I gave should work if you cut and paste:
Select dbo.Largest(Q1_1, dbo.Largest(Q1_2, dbo.Largest(Q1_3,Q1_4)))
blindman
...by the way, switching from computer professional to rodeo clown is considered at best a lateral move. Aim for Ringmaster, or some other management role to further your career.sql
Monday, March 26, 2012
easy questions related with A-P cluster
Hi everyone,
I'm just a newbie with clustering.
We've got a 64-bit A-P cluster running with Sql25k 64-bit too.
Each node own two local network adapters. One of them (the one currently is Active) own three IP and the another one only one.
Why? From CLUADMIN I see as one of these three is set as Cluster IP Address in Active Resources. So I see the following configuration for the Active node:
Adapter 1: IP public for Active Directory
Adapter2:IP1 (reserved or ?)
IP2 (used as SQL IP Address 1 in Active Resources)
IP3 (used as Cluster IP Address in Active Resources)
Passive node:
Adapter 1: IP public for Active Directory
Adapter2:IP (only have one ?)
Is this correct/necessary? Does anyone have similar configuration?
Thanks for your time,
Does anyone have any idea?|||
This issue has already solved.
Ip1: physical node
Ip2: virtual cluster
Ip3: virtual Sql
easy questions related with A-P cluster
Hi everyone,
I'm just a newbie with clustering.
We've got a 64-bit A-P cluster running with Sql25k 64-bit too.
Each node own two local network adapters. One of them (the one currently is Active) own three IP and the another one only one.
Why? From CLUADMIN I see as one of these three is set as Cluster IP Address in Active Resources. So I see the following configuration for the Active node:
Adapter 1: IP public for Active Directory
Adapter2:IP1 (reserved or ?)
IP2 (used as SQL IP Address 1 in Active Resources)
IP3 (used as Cluster IP Address in Active Resources)
Passive node:
Adapter 1: IP public for Active Directory
Adapter2:IP (only have one ?)
Is this correct/necessary? Does anyone have similar configuration?
Thanks for your time,
Does anyone have any idea?|||
This issue has already solved.
Ip1: physical node
Ip2: virtual cluster
Ip3: virtual Sql
Easy Newbie reporting services question
For example: Fields!<FieldName>.Value.Length
Is very useful. I found it on this user group but could not find it in
the documentation (online books). The expression painter does not seem
to be much help.
Where can I find more information on what I can put into expressions?
Thanks in advance :)Hi,
Search for "Using Expressions in Reporting Services" and
"Expression Examples in Reporting Services" this on gives you how to use it
with examples.
Hope you might have seen this. but this is very useful. Moreover you can get
the help from "Edit Expression" form. click on any function. you can get the
help.
Amarnath
"mzwilli" wrote:
> I'd like to learn more about what I can put in expressions.
> For example: Fields!<FieldName>.Value.Length
> Is very useful. I found it on this user group but could not find it in
> the documentation (online books). The expression painter does not seem
> to be much help.
> Where can I find more information on what I can put into expressions?
> Thanks in advance :)
>
Easy newbie question
elegant solution, as I am completely new at this so don't really know
what to look for. I have the following table:
Id Cat
1 A
2 B
3 C
4 A
5 B
6 B
7 C
...
I would like to create a new column, CatId, which has a value n, which
is the nth appearance of the record's category (ordered by Id). In
this case it would be 1,1,1,2,2,3,2 because e.g. Id=6 is the third
appearance of the letter B so it would equal 3.
How could I do this in T-SQL?On 19 Mar 2007 11:26:36 -0700, jrpfinch wrote:
Quote:
Originally Posted by
>Sorry if this seems too easy to be interesting - I have yet to find an
>elegant solution, as I am completely new at this so don't really know
>what to look for. I have the following table:
>
>Id Cat
>1 A
>2 B
>3 C
>4 A
>5 B
>6 B
>7 C
>...
>
>I would like to create a new column, CatId, which has a value n, which
>is the nth appearance of the record's category (ordered by Id). In
>this case it would be 1,1,1,2,2,3,2 because e.g. Id=6 is the third
>appearance of the letter B so it would equal 3.
>
>How could I do this in T-SQL?
Hi jrpfinch,
SELECT a.Id, a.Cat,
COUNT(*) AS CatId
FROM YourTable AS a
INNER JOIN YourTable AS b
ON b.Cat = a.Cat
AND b.Id <= a.Id
GROUP BY a.Id, a.Cat;
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On 19 Mar, 18:26, "jrpfinch" <jrpfi...@.gmail.comwrote:
Quote:
Originally Posted by
Sorry if this seems too easy to be interesting - I have yet to find an
elegant solution, as I am completely new at this so don't really know
what to look for. I have the following table:
>
Id Cat
1 A
2 B
3 C
4 A
5 B
6 B
7 C
...
>
I would like to create a new column, CatId, which has a value n, which
is the nth appearance of the record's category (ordered by Id). In
this case it would be 1,1,1,2,2,3,2 because e.g. Id=6 is the third
appearance of the letter B so it would equal 3.
>
How could I do this in T-SQL?
To ensure you get a good answer to a question like this always state
what version you are using and include a CREATE TABLE statement that
defines the key(s) in your table.
Here's my guess based on the assumption that (Id, Cat) is unique. Note
that if CatID is always to be based on the values of the other two
columns then it might make more sense NOT to include it in the table.
Instead compute it whenever the results are displayed.
UPDATE tbl
SET CatID =
(SELECT COUNT(*)
FROM tbl AS t
WHERE Cat = tbl.Cat
AND ID <= tbl.ID);
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--
Monday, March 19, 2012
Dynamically name folders
Apologies in advance for the newbie question. I'm new to Integration Services and am trying to build my first project.
I want to dynamically create folders based on the results of a stored procedure. The resultset is limited to one column but can contain multiple rows. (It returns a list of 8-character strings representing dates.) This returned value needs to be combined with the path where the new folders should be created.
Can someone point me in the right direction? I've run the stored procedure and put the resultset into a RecordSet Destination that uses a variable of type Object. (At least, I think I've done it correctly.) But I have not been able to figure out how to access this data to actually create the folders.
Thanks for the help,
Chris
Use a foreach loop.http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx|||
Thanks!!!
That was exactly what I needed and worked like a charm.
Chris
Sunday, March 11, 2012
Dynamically Create Columns
Hi, i'm a newbie to reporting services. May i know if it is possible to dynamically create columns for a report?
I know that there's a way to hide the columns on the fly. However, for my case, i will only know the amount and the detail information (column name etc..) of columns while the report is running.
Thanks in advance.
I think i have posted the message in the wrong forum. I should post it in the SQL 2k forum. Sorry|||Maybe a matrix could help...
|||The only way to create columns on the fly is to use the matrix control. In this mode, the columns are groups in your data.
RS 2005 includes an 'execute on the fly' method on the execution web service so you could construct a dynamic report and pass it to the service to run.
|||Can you please point us to some samples of this "execute on the fly method"?Dynamically Create Columns
Hi, i'm a newbie to reporting services. May i know if it is possible to dynamically create columns for a report?
I know that there's a way to hide the columns on the fly. However, for my case, i will only know the amount and the detail information (column name etc..) of columns while the report is running.
Thanks in advance.
I think i have posted the message in the wrong forum. I should post it in the SQL 2k forum. Sorry|||Maybe a matrix could help...
|||The only way to create columns on the fly is to use the matrix control. In this mode, the columns are groups in your data.
RS 2005 includes an 'execute on the fly' method on the execution web service so you could construct a dynamic report and pass it to the service to run.
|||Can you please point us to some samples of this "execute on the fly method"?Sunday, February 26, 2012
Dynamic Tool Configuration
Newbie here wanting to get information, links, opinions on how to configure a simple data flow from code. Here's my example. I have a CSV input tool, a SORT tool, and a CSV output tool. I want to call this package from an application running C#. Can I access this package like DataPack("filename","sort","output filename") from the object model? Or does XML have to get involved? Can I dynamically swap out the input or output tools? Any links to the UML mockup of the object model? Just getting started here.
Thanks in advance,
Mitch
Friday, February 17, 2012
Dynamic SQL statement
help appreciated.
Declare @.sqlStmt nvarchar(500)
Declare @.PatNo int
Declare @.Range int
set @.PatNO = 240
Set @.Range = 36000
--EXEC --('insert Into ' + @.TempVS +
SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
Result, Units, abn, Range
FROM PatLabVal2 where PatNo = @.PatNo '
EXEC @.sqlstmt
I get an error :
Server: Msg 2812, Level 16, State 62, Line 11
Could not find stored procedure 'Select Distinct patNo, DateDrawn,
Sort,TestnameDisplay,
Result, Units, abn, Range
FROM PatLabVal2 where PatNo = @.PatNo '.
Thanks for the help,
Stephen K. MiyasatoDon't call Exec. Use sp_execsql instead. Look it up in books online for
examples, but you're about 90% of the way to implementing it.
Regards
Colin Dawson
www.cjdawson.com
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:O5jeNtScGHA.3856@.TK2MSFTNGP03.phx.gbl...
> I'm still a newbie and can't get this dynamic SQL statement to work. Any
> help appreciated.
> Declare @.sqlStmt nvarchar(500)
> Declare @.PatNo int
> Declare @.Range int
> set @.PatNO = 240
> Set @.Range = 36000
> --EXEC --('insert Into ' + @.TempVS +
> SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '
> EXEC @.sqlstmt
> I get an error :
> Server: Msg 2812, Level 16, State 62, Line 11
> Could not find stored procedure 'Select Distinct patNo, DateDrawn,
> Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '.
> Thanks for the help,
> Stephen K. Miyasato
>|||Thanks for the help.
Stephen K. Miyasato
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:oH47g.65228$wl.53809@.text.news.blueyonder.co.uk...
> Don't call Exec. Use sp_execsql instead. Look it up in books online for
> examples, but you're about 90% of the way to implementing it.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
> news:O5jeNtScGHA.3856@.TK2MSFTNGP03.phx.gbl...
>|||Just adding to Colin's comment.
don't use nvarchar unless u are sure about unicode character. use varchar.
and
for executing dynamic sql
use
EXEC(@.sqlstmt)
again. This is just for the syntax.
Use sp_executesql. Its better in code reuse and does the same job from a
developers standpoint.
--
"Stephen K. Miyasato" wrote:
> I'm still a newbie and can't get this dynamic SQL statement to work. Any
> help appreciated.
> Declare @.sqlStmt nvarchar(500)
> Declare @.PatNo int
> Declare @.Range int
> set @.PatNO = 240
> Set @.Range = 36000
> --EXEC --('insert Into ' + @.TempVS +
> SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '
> EXEC @.sqlstmt
> I get an error :
> Server: Msg 2812, Level 16, State 62, Line 11
> Could not find stored procedure 'Select Distinct patNo, DateDrawn,
> Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '.
> Thanks for the help,
> Stephen K. Miyasato
>
>|||Stephen K. Miyasato (miyasat@.flex.com) writes:
> I'm still a newbie and can't get this dynamic SQL statement to work. Any
> help appreciated.
> Declare @.sqlStmt nvarchar(500)
> Declare @.PatNo int
> Declare @.Range int
> set @.PatNO = 240
> Set @.Range = 36000
> --EXEC --('insert Into ' + @.TempVS +
> SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '
> EXEC @.sqlstmt
> I get an error :
> Server: Msg 2812, Level 16, State 62, Line 11
> Could not find stored procedure 'Select Distinct patNo, DateDrawn,
> Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '.
You need parentheses after EXEC to execute dynamic SQL. Now you are
trying to execute a stored procedure of which the name is in @.sqlstmt.
Again, I recommend you to read my article
http://www.sommarskog.se/dynamic_sql.html. That will give you the
answer to this question, it will also sort out some problems that
you have run into yet. And you may even find out that you don't need
dynamic SQL at all.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Dynamic SQL Newbie needs urgent help
it. I have a field name of navigationPK to get the field they want. Only
problem is is that the navigationPK can be a primary key from any number of
tables.
Scenario......
Have a usertask displayed on dashboard....need to do a report on all
overdue tasks. Each task has a particular navigationpk. This navpk can be
linked to three or four tables. ie....navpk 1189134 could be the pk in a
tpp table, rvc table or a tr table. Need to ascertain which one it is so
that I can get the relevant additional field from each table.
Kinda hard to explain. But if anybody wants anymore info then please ask.
I was told to do dynamic sql...only prob is I have no idea where to start
and how to fit into existing script. Below is my initial script....works
fine so far. Needs tidying up I know, but essentially it works.
select fulllegalname, p.Id AS PersonId, dali.Name As DDCMAreaName,
ut.UserName As UTUserName, ut.MessageBody AS Task, ut.NavigationPK as NavPK,
ut.DueDate As DueDate
--h.id as HerdId
from person p
--INNER JOIN personherdrole phr on p.id = phr.personid
--INNER JOIN herd h on phr.Herdid = h.id
INNER JOIN personddcmarealink pdal on pdal.personId = p.Id
INNER JOIN ddcmarealookupitem dali on pdal.ddcmarealookupitemId = dali.Id
INNER JOIN usertask ut on ut.UserName = p.UserName
where (ut.DeletedDate IS NULL) and (ut.DueDate < getdate())
The additional field required is the herdid, and this herdid could be from
the tpp table, rvc table, tr table .
Help please. Going round in circles and not sure where to start.Article about dynamic sql
http://www.sommarskog.se/dynamic_sql.html
You did not describe the rules as to how you know which table it will be in.
Can it be in multiple tables but you only want a specific one?
Or the HerdID will only be in one of the tables?
If so
select fulllegalname, p.Id AS PersonId, dali.Name As DDCMAreaName,
ut.UserName As UTUserName, ut.MessageBody AS Task, ut.NavigationPK as NavPK,
ut.DueDate As DueDate
--h.id as HerdId
from person p
INNER JOIN personherdrole phr on p.id = phr.personid
INNER JOIN herd h on phr.Herdid = h.id
INNER JOIN personddcmarealink pdal on pdal.personId = p.Id
INNER JOIN ddcmarealookupitem dali on pdal.ddcmarealookupitemId = dali.Id
INNER JOIN usertask ut on ut.UserName = p.UserName
Inner JOIN (SELECT newfield, HerdID
FROM tpp
UNION
SELECT newfield, HerdID
FROM rvc
UNION
SELECT newfield, HerdID
FROM tr ) DerivedTable on DerivedTable.HerdID=HERD.id
where (ut.DeletedDate IS NULL) and (ut.DueDate < getdate())
No Dynamic required
"Nat Johnson" wrote:
> Have a report that is working fine. Customer wants to add another field to
> it. I have a field name of navigationPK to get the field they want. Only
> problem is is that the navigationPK can be a primary key from any number of
> tables.
> Scenario......
> Have a usertask displayed on dashboard....need to do a report on all
> overdue tasks. Each task has a particular navigationpk. This navpk can be
> linked to three or four tables. ie....navpk 1189134 could be the pk in a
> tpp table, rvc table or a tr table. Need to ascertain which one it is so
> that I can get the relevant additional field from each table.
> Kinda hard to explain. But if anybody wants anymore info then please ask.
> I was told to do dynamic sql...only prob is I have no idea where to start
> and how to fit into existing script. Below is my initial script....works
> fine so far. Needs tidying up I know, but essentially it works.
> select fulllegalname, p.Id AS PersonId, dali.Name As DDCMAreaName,
> ut.UserName As UTUserName, ut.MessageBody AS Task, ut.NavigationPK as NavPK,
> ut.DueDate As DueDate
> --h.id as HerdId
>
> from person p
> --INNER JOIN personherdrole phr on p.id = phr.personid
> --INNER JOIN herd h on phr.Herdid = h.id
> INNER JOIN personddcmarealink pdal on pdal.personId = p.Id
> INNER JOIN ddcmarealookupitem dali on pdal.ddcmarealookupitemId = dali.Id
> INNER JOIN usertask ut on ut.UserName = p.UserName
> where (ut.DeletedDate IS NULL) and (ut.DueDate < getdate())
>
> The additional field required is the herdid, and this herdid could be from
> the tpp table, rvc table, tr table .
> Help please. Going round in circles and not sure where to start.
>|||1. create your select statement
2 run the select statement and generate fields
3. generate report
last but not least turn the sql into a string after you have everything
working. us a report parameter to pass the dynamic part to the querry.
like this
="select * from " + Parameters!company.value
its that easy.
rmartinez wrote:
>Article about dynamic sql
>http://www.sommarskog.se/dynamic_sql.html
>You did not describe the rules as to how you know which table it will be in.
>Can it be in multiple tables but you only want a specific one?
>Or the HerdID will only be in one of the tables?
>If so
>select fulllegalname, p.Id AS PersonId, dali.Name As DDCMAreaName,
>ut.UserName As UTUserName, ut.MessageBody AS Task, ut.NavigationPK as NavPK,
>ut.DueDate As DueDate
>--h.id as HerdId
>from person p
>INNER JOIN personherdrole phr on p.id = phr.personid
>INNER JOIN herd h on phr.Herdid = h.id
>INNER JOIN personddcmarealink pdal on pdal.personId = p.Id
>INNER JOIN ddcmarealookupitem dali on pdal.ddcmarealookupitemId = dali.Id
>INNER JOIN usertask ut on ut.UserName = p.UserName
>Inner JOIN (SELECT newfield, HerdID
> FROM tpp
> UNION
> SELECT newfield, HerdID
> FROM rvc
> UNION
> SELECT newfield, HerdID
> FROM tr ) DerivedTable on DerivedTable.HerdID=HERD.id
>where (ut.DeletedDate IS NULL) and (ut.DueDate < getdate())
>No Dynamic required
>> Have a report that is working fine. Customer wants to add another field to
>> it. I have a field name of navigationPK to get the field they want. Only
>[quoted text clipped - 31 lines]
>> Help please. Going round in circles and not sure where to start.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1