foreign key

Hi

is it a good practice to create FK on each & every column in a table whose values are taken from master table.

should I create or not.

If I create will that be heavy i.e what about memory consumption ?

pls reply

Thanks

Shubhangi1. You can foreign keys.

or

2. You can check through code. By using Inner join table name
on parenttabel.field=childtable.field

ro

3.Before inserting a record in child table, check whether it exists in the parent table through code by using if exists or if (select count(*) from tablename where
...)=0

or

if exists(select 1 from tablename where ...)

Foreach variable cannot be applied

I have created a foreach container to capture values from a query and run a script for each line. For some reason though I can't get past the variable mapping.

The SQL Task that is linked to the Foreach container is somthing like this:

SELECT str1, str2, str2, nValue1, nValue2

FROM MyTable

In the SQL Task I've set the following

ResultSet = Full Result Set

Connection Type = OLE DB

Result Name = 0

I've created the following Variables with a Package scope

s1 as String

s2 as String

s3 as String

n1 as Double

n2 as Double

When I run the package I get the following errors

Foreach variable mapping number 4 to variable "User : : n1" cannot be applied

Foreach variable mapping number 5 to variable "User : : n2" cannot be applied

The data type of nValue1 and nValue2 in MyTable are both numeric(19,4)

In setting the variable types in the package it seems that the closest to numeric(19,4) would be double. Is this why it is failing? Is there any way around it? I just need to pass the variables which are numeric and have decimal places.

Any insight would be greatly appreciated.

Regards,

Bill

See if this blog post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

|||

Thanks for the pointer, I found that if I cast the numeric fields in my query to float that the mapping seemed to pass the variables ok.

Regards,

Bill

Forcing size of a field

Hi,
I have a field x varchar(6)
I want force the values at least at 4 char no less
how can i do it?
NULL must be still valid.
Thanks, FilippoUse a CHECK constraint:
CREATE TABLE #t(c1 varchar(6) NULL)
ALTER TABLE #t ADD CONSTRAINT cnstname CHECK (LEN(c1) >= 4)
GO
INSERT INTO #t (c1) VALUES('1234')
GO
INSERT INTO #t (c1) VALUES('123')
GO
INSERT INTO #t (c1) VALUES(NULL)
GO
SELECT * FROM #t
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Filippo Bettinaglio" <anonymous@.discussions.microsoft.com> wrote in message
news:1ed701c53e80$f4885ac0$a601280a@.phx.gbl...
> Hi,
> I have a field x varchar(6)
> I want force the values at least at 4 char no less
> how can i do it?
> NULL must be still valid.
> Thanks, Filippo|||Use a CHECK constraint:
ALTER TABLE Filippo
ADD CONSTRAINT CK_Filippo__len_x_gte_4
CHECK (LEN(x) >=4)
Note that the LEN of NULL is NULL, and NULL>=4 returns UNKNOWN, and that
doesn't violate the CHECK constraint. CHECK constraints (and constraints in
general) are only violated if the expression they check returns FALSE.
Jacco Schalkwijk
SQL Server MVP
"Filippo Bettinaglio" <anonymous@.discussions.microsoft.com> wrote in message
news:1ed701c53e80$f4885ac0$a601280a@.phx.gbl...
> Hi,
> I have a field x varchar(6)
> I want force the values at least at 4 char no less
> how can i do it?
> NULL must be still valid.
> Thanks, Filippo

forcing column to appear

I have a matrix that shows figures by year, broken down by months (1-12).
When the query runs and that particular month has not values, the column is
not displayed. I previously came across something regarding the use of a
function to force the columns to appear but can't seem to find it again.
Anyone have a suggestion for doing this? It would be similar to how the PIVOT
in access works.On Jun 6, 10:08 am, brian <b...@.discussions.microsoft.com> wrote:
> I have a matrix that shows figures by year, broken down by months (1-12).
> When the query runs and that particular month has not values, the column is
> not displayed. I previously came across something regarding the use of a
> function to force the columns to appear but can't seem to find it again.
> Anyone have a suggestion for doing this? It would be similar to how the PIVOT
> in access works.
I traditionally look for the columns (value in the pivot column) that
I am expecting in the dataset and if they do not appear union an empty
record with the column name to the returned dataset (as part of the
stored procedure/query that is sourcing the report). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Force prompt when using default parameters

I wrote an sp and assigned static values to fields for easy testing, after
that I created the parameters and assigned default values to them, now
everytime I click preview I don't get a prompt for new values, the report
automatically runs with the defaults.
I want to be able to use the default values most of the time but also want
the option to change them if needed. The only option is to stop the report
and enter different values. This is strange because I have other reports that
have all default values but give me a prompt in preview.Someone answered this a few ones back.
Add a boolean "Run Report" parameter to the report with no default.
-T
"Ash_Cat" <AshCat@.discussions.microsoft.com> wrote in message
news:3974BD37-7DCD-4FBF-8602-F660A7ADE92C@.microsoft.com...
>I wrote an sp and assigned static values to fields for easy testing, after
> that I created the parameters and assigned default values to them, now
> everytime I click preview I don't get a prompt for new values, the report
> automatically runs with the defaults.
> I want to be able to use the default values most of the time but also want
> the option to change them if needed. The only option is to stop the report
> and enter different values. This is strange because I have other reports
> that
> have all default values but give me a prompt in preview.|||Cool! Thx Tim
"Tim Dot NoSpam" wrote:
> Someone answered this a few ones back.
> Add a boolean "Run Report" parameter to the report with no default.
> -T
> "Ash_Cat" <AshCat@.discussions.microsoft.com> wrote in message
> news:3974BD37-7DCD-4FBF-8602-F660A7ADE92C@.microsoft.com...
> >I wrote an sp and assigned static values to fields for easy testing, after
> > that I created the parameters and assigned default values to them, now
> > everytime I click preview I don't get a prompt for new values, the report
> > automatically runs with the defaults.
> > I want to be able to use the default values most of the time but also want
> > the option to change them if needed. The only option is to stop the report
> > and enter different values. This is strange because I have other reports
> > that
> > have all default values but give me a prompt in preview.
>
>

Foramtting chart axis colour using expression

I have a chart on a report that shows value in ￡ on the Y Axis. The values can be both positive and negative. I would like to have the negative labels to show in red and the positive in black. What is the expression syntax to achieve this? i know it will be an IIF statement, but what is the syntax to show the Y Axis label as <0?

eg if i was doing this for a field it would be iif(Field!Fieldname.value <0, "RED","BLACK") but what would i replace the FIELD!Fieldname.vaue with so the expression would change the Y Axis label.

Thanks in advance for any help

Dave Munro

Hi Dave,

I'm a brazillian guy, but i try to help you !

First of all, You will have to create a code to fill the bars.

REPORT - REPORT PROPERTIES - CODE (tab)

Function ColorBar(VlField) As String
Dim Bar_Colour as String
if VlField > 0 then
Bar_Colour = "BLACK"
Else
Bar_Colour = "RED"
End if

Return Bar_Colour

Afther that, click in chart and set the properties.

click in TAB (VALUES) - VALUE - edit - Appearance (Tab) - Series Style - Fill - expression (color)

=code.ColorBar(Fields!XXXXXX.Value)

OK....

Bye,

Nitrini

Foramtting chart axis colour using expression

I have a chart on a report that shows value in ￡ on the Y Axis. The values can be both positive and negative. I would like to have the negative labels to show in red and the positive in black. What is the expression syntax to achieve this? i know it will be an IIF statement, but what is the syntax to show the Y Axis label as <0?

eg if i was doing this for a field it would be iif(Field!Fieldname.value <0, "RED","BLACK") but what would i replace the FIELD!Fieldname.vaue with so the expression would change the Y Axis label.

Thanks in advance for any help

Dave Munro

Hi Dave,

I'm a brazillian guy, but i try to help you !

First of all, You will have to create a code to fill the bars.

REPORT - REPORT PROPERTIES - CODE (tab)

Function ColorBar(VlField) As String
Dim Bar_Colour as String
if VlField > 0 then
Bar_Colour = "BLACK"
Else
Bar_Colour = "RED"
End if

Return Bar_Colour

Afther that, click in chart and set the properties.

click in TAB (VALUES) - VALUE - edit - Appearance (Tab) - Series Style - Fill - expression (color)

=code.ColorBar(Fields!XXXXXX.Value)

OK....

Bye,

Nitrini

For XML: create elements using cell values

Hi All,
If have a sql table with 2 columns and 2 rows with values
[["col1row1","col2row1"],["col1row2","col2row2"]].

Using t-SQL with "for xml"
How can i create a xml where the cell values (not column names) appear
as elements?
eg:
<col1row1>col2row1</col1row1>
<col1row2>col2row2</col1row2
Thanks,

slyi-- It can be done, but remember that you will have to
-- escape all the XML yourself

create table #test(
col1 varchar(8),
col2 varchar(8))

insert into #test(col1,col2)
values ('col1row1','col2row1')
insert into #test(col1,col2)
values ('col1row2','col2row2')

select 1 as Tag,
null as Parent,
'<'+col1+'>'+col2+'</'+col1+'>' as [TestNode!1!!xml]
from #test

order by Tag,[TestNode!1!!xml]
for xml explicit

drop table #test|||Thanks thats exactly what i needed to know|||On closer examination this wont work it gives

<TestNode><col1row1>col2row1</col1row1></TestNode>
<TestNode><col1row2>col2row2</col1row2></TestNode>
while i need something like
<TestNode>
<col1row1>col2row1</col1row1>
<col1row2>col2row2</col1row2>
</TestNode|||Unless someone else knows better, you're out of luck. Perhaps
you could look at redesigning the XML you are generating
and then apply an XSL transformation at the client.|||Thanks Mark. Could i create a temp table, with the cell values as
columns and build a sql xml query or loop from there?
Although im not too sure if that would work, very efficiently?|||(adrianca@.gmail.com) writes:
> Thanks Mark. Could i create a temp table, with the cell values as
> columns and build a sql xml query or loop from there?
> Although im not too sure if that would work, very efficiently?

I can't see that you can do this in SQL 2000 at all. Well, you can
build an nvarchar string that has the XML, and forego FOR XML
altogether, but if you exceed 4000 characters you lose anyway.

I think you need to build this document client-side.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||--something like below is what i was thinking but is it efficent?
--as the client side asp code works out very slow thats why i want to
do it on the sql server if possible

create table #test(
col1 varchar(8),
col2 varchar(8))

insert into #test(col1,col2)
values ('col1row1','col2row1')
insert into #test(col1,col2)
values ('col1row2','col2row2')

create table #xmltree( xmlblob text)
INSERT INTO #xmltree VALUES ('<table>')

Declare @.sqlq varchar(4000)
DECLARE @.textptr varbinary(16)
DECLARE @.bigtext varchar(8000)
DECLARE @.textlen int
DECLARE @.col1 varchar(32), @.col2 varchar(32)

SELECT @.textptr=TEXTPTR(xmlblob) FROM #xmltree

DECLARE tst_cursor CURSOR FOR select * from #test
OPEN tst_cursor
FETCH NEXT FROM tst_cursor into @.col1, @.col2
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.bigtext='<'+ @.col1 + '>' +@.col2+'</'+ @.col1 + '>'
set @.textlen =(SELECT DATALENGTH(xmlblob) FROM #xmltree )
UPDATETEXT #xmltree.xmlblob @.textptr @.textlen 0 @.bigtext
FETCH NEXT FROM tst_cursor into @.col1, @.col2
END
CLOSE tst_cursor
DEALLOCATE tst_cursor
SET @.bigtext='</table>'
set @.textlen =(SELECT DATALENGTH(xmlblob) FROM #xmltree )
UPDATETEXT #xmltree.xmlblob @.textptr @.textlen 0 @.bigtext

select xmlblob from #xmltree

drop table #test
drop table #xmltree|||(adrianca@.gmail.com) writes:
> --something like below is what i was thinking but is it efficent?

More to the point: does it work?

> create table #xmltree( xmlblob text)
> INSERT INTO #xmltree VALUES ('<table>')

There is not really any way go get the xml from FOR XML into the table.
Well, you can get it to the client, and then INSERT back. Please don't
that. You're wasting bandwidth.

> --as the client side asp code works out very slow thats why i want to
> do it on the sql server if possible

For this sort of task, I would expect VBscript to be faster than T-SQL,
since we are only doing string manipulation.

You could write a program in C or C# for the task, but then you would have
to pass the XML string to the C program in some way. If you go by file,
you probably lose on the swings what you gain on the roundabout.

I should add the disclaimer that I have no knowledge about ASP
programming.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||asp 6 and asp.net both took about 10 sec to create the xml client side
from a sql table for a 50k table to xml

Using this method it now takes about 2 sec's by just displaying
resultset.

<%@. Page Language="C#" %
<%@. Import Namespace="System.Data.SqlClient" %
<script runat="server">
SqlConnection sqlConnection1;
SqlCommand sqlCommand1;

void Page_Load(Object Sender, EventArgs e) {

sqlConnection1 = new System.Data.SqlClient.SqlConnection();
sqlCommand1 = new System.Data.SqlClient.SqlCommand();
sqlConnection1.ConnectionString = "some connection details";
sqlConnection1.Open();
sqlCommand1.Connection = this.sqlConnection1;
sqlCommand1.CommandText = "sp_getaxml_dataisland";
Response.ContentType = "text/xml";
Response.Write(sqlCommand1.ExecuteScalar().ToStrin g());

}
</script
For me thats a performance gain worth taking.|||(adrianca@.gmail.com) writes:
> asp 6 and asp.net both took about 10 sec to create the xml client side
> from a sql table for a 50k table to xml

Just to check: how did you get the data to the client? You did get
all data into a dataset didn't you?

> sqlCommand1.CommandText = "sp_getaxml_dataisland";

sp_ is a prefix that is reserved for system stored procedure, and
SQL Server first looks in master for these. You should not use it
for your own code.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>Just to check: how did you get the data to the client? You did get
>all data into a dataset didn't you?
Since the resultset is just one huge SQL Text datatype,
i just wrote it directly to the page, no need for the overhead of
creating a .net dataset object,
and then a javascript dataisland reads it directly.

eg: <xml id="my-dataisland" src="http://pics.10026.com/?src=getdataisland.aspx" /
>> sqlCommand1.CommandText = "sp_getaxml_dataisland";

>sp_ is a prefix that is reserved for system stored procedure, and
>SQL Server first looks in master for these. You should not use it
>for your own code.
Thanks for the tip i didnt know that.
Do you know if first checks the master table, will that slow down the
request correct / target sp?
I had thought you needed to put "master.dbo.sp_" to access a master sp?

Thanks for your help.|||(adrianca@.gmail.com) writes:
> Since the resultset is just one huge SQL Text datatype,
> i just wrote it directly to the page, no need for the overhead of
> creating a .net dataset object,
> and then a javascript dataisland reads it directly.

Javascript is maybe not the fastest. Can you save to a file, and run a
program in a non-interpreted langauge?

> Do you know if first checks the master table, will that slow down the
> request correct / target sp?
> I had thought you needed to put "master.dbo.sp_" to access a master sp?

In such case "sp_help" would not work. In fact when you say

somedatabase.dbo.sp_help tbl

what you get information about is somedatabase.dbo.tbl.

Exactly what happens is difficult describe, because it changes every
now and then. But if Microsoft would ship a system procedure called
sp_getaxml_dataisland, you would be in for a nasty surprise.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

for xml hierarchy

Hi

I have a table that looks like this

declare @.VC table ( V varchar(100), VC int, depth tinyint)
insert into @.VC values ( 'TN', 1, 1)
insert into @.VC values ( 'TN', 2, 2);

and I have for xml query

select
V as @.value,
(
select VC as '@.value' from @.VC pe where pe.V = n.V
for xml path ('Value'), root('Values'), type
) as ME
from ( select distinct VC from @.VC ) n
for xml path ('Value'), root('Values')

that gives me something like this

<Values>
<Value value="TN">
<ME>
<Values>
<Value value="1" />
<Value value="2" />
</Values>
</ME>
</Value>
</Values>

However I need to reorder the xml to look like this according to depth

<Values>
<Value value="TN">
<ME>
<Values depth = 1>
<Value value="1" />
</Values>
</ME>
<ME>
<Values depth =2>
<Value value="2" />
</Values>
</ME>
</Value>
</Values>

the problem I am having is that depth 2 is not below depth 1 node, is on the same depth with the depth attribute diff.

Is there some way to write the for xml to do this

thanks

P

select
V as "@.value",
(
select pe.depth as "Values/@.depth",
pe.VC as "Values/Value/@.value"
from @.VC pe where pe.V = n.V
for xml path('ME'),type
)
from ( select distinct V from @.VC ) n
for xml path ('Value'), root('Values')

FOR XML EXPLICIT issue

Hi guys:
I'm in an urgent need to know if we can dynamically alter values in
querying tables using FOR XML EXPLICIT. In the following sample, I have
3 tabels involved vis_Rule, vis_IF, and vis_AND.
Table Structure:
Table: vis_Rule
Column1: RuleId, Column2: Name, Column3: Priority, Column4: Active
Table: vis_IF
Column1: IFId, Column2: RuleId -> Foreign Key to RuleId in vis_Rule
Table: vis_AND
Column1: AndId, Column2: IFId -> Foreign Key to IFId in vis_IF
++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++
In the following stored proc, can we grab the RuleId of the first
SELECT statement and pass it to the next SELECT statement such that it
extracts only the desired rows? In other words, can we dynamically
assign values from one part of the SELECT statement to the next?
SELECT TOP 1
1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id],
NULL as [compare!4!id],
NULL as [compare!4!operator]
FROM vis_Rule r
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL,
NULL,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
NULL,
NULL,
NULL,
NULL,
if.IFId,
[and].AndId,
NULL,
NULL
FROM vis_AND [and], vis_IF if
where [and].IFId = if.IFId
FOR XML EXPLICIT
GO
Thanks in advance.My apologies for the late reply, but I was on vacation the last couple of
ws.
I am not quite sure what you try to acheive. Do you want to only provide the
nesting of the tree for a given ruleID?
In that case try:
CREATE Table vis_Rule (RuleId int,Name nvarchar(40), Priority int, Active
bit)
go
insert into vis_Rule values (1, N'r1', 1, 1)
insert into vis_Rule values (2, N'r2', 2, 1)
go
CREATE Table vis_IF (IFId int, RuleId int --> Foreign Key to RuleId in
vis_Rule
)
go
insert into vis_IF values (1, 1)
insert into vis_IF values (2, 1)
insert into vis_IF values (3, 1)
insert into vis_IF values (4, 2)
go
CREATE Table vis_AND (AndId int, IFId int --> Foreign Key to IFId in vis_IF
)
insert into vis_AND values (1, 1)
insert into vis_AND values (2, 1)
insert into vis_AND values (3, 2)
insert into vis_AND values (4, 2)
insert into vis_AND values (5, 3)
insert into vis_AND values (6, 4)
go
declare @.rid int
set @.rid = 1
SELECT TOP 1
1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id]
FROM vis_Rule r
where r.RuleId = @.rid
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
AND r.RuleId = @.rid
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
[if].RuleId,
NULL,
NULL,
NULL,
[if].IFId,
[and].AndId
FROM vis_AND [and], vis_IF [if]
where [and].IFId = [if].IFId
AND [if].RuleId = @.rid
ORDER BY [rule!1!Id], [if!2!id], Parent
FOR XML EXPLICIT
If you want it for all rules, try:
SELECT 1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id]
FROM vis_Rule r
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
[and].AndId
FROM vis_AND [and], vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
AND r.RuleId = [if].RuleId
AND [and].IFId = [if].IFId
ORDER BY [rule!1!Id], [if!2!id], Parent
FOR XML EXPLICIT
Best regards
Michael
<shamod@.gmail.com> wrote in message
news:1122527565.227837.240190@.g43g2000cwa.googlegroups.com...
> Hi guys:
> I'm in an urgent need to know if we can dynamically alter values in
> querying tables using FOR XML EXPLICIT. In the following sample, I have
> 3 tabels involved vis_Rule, vis_IF, and vis_AND.
> Table Structure:
> Table: vis_Rule
> Column1: RuleId, Column2: Name, Column3: Priority, Column4: Active
> Table: vis_IF
> Column1: IFId, Column2: RuleId -> Foreign Key to RuleId in vis_Rule
> Table: vis_AND
> Column1: AndId, Column2: IFId -> Foreign Key to IFId in vis_IF
> ++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++
> In the following stored proc, can we grab the RuleId of the first
> SELECT statement and pass it to the next SELECT statement such that it
> extracts only the desired rows? In other words, can we dynamically
> assign values from one part of the SELECT statement to the next?
> SELECT TOP 1
> 1 AS Tag,
> NULL AS Parent,
> r.RuleId as [rule!1!Id],
> r.Name as [rule!1!name],
> r.Priority as [rule!1!priority],
> r.Active as [rule!1!active],
> NULL as [if!2!id],
> NULL as [and!3!id],
> NULL as [compare!4!id],
> NULL as [compare!4!operator]
> FROM vis_Rule r
> UNION ALL
> SELECT 2 AS Tag,
> 1 AS Parent,
> r.RuleId,
> NULL,
> NULL,
> NULL,
> [if].IFId,
> NULL,
> NULL,
> NULL
> FROM vis_IF [if], vis_Rule r
> WHERE [if].RuleId = r.RuleId
> UNION ALL
> SELECT 3 AS Tag,
> 2 AS Parent,
> NULL,
> NULL,
> NULL,
> NULL,
> if.IFId,
> [and].AndId,
> NULL,
> NULL
> FROM vis_AND [and], vis_IF if
> where [and].IFId = if.IFId
> FOR XML EXPLICIT
> GO
> Thanks in advance.
>

FOR XML EXPLICIT issue

Hi guys:
I'm in an urgent need to know if we can dynamically alter values in
querying tables using FOR XML EXPLICIT. In the following sample, I have
3 tabels involved vis_Rule, vis_IF, and vis_AND.
Table Structure:
Table: vis_Rule
Column1: RuleId, Column2: Name, Column3: Priority, Column4: Active
Table: vis_IF
Column1: IFId, Column2: RuleId -> Foreign Key to RuleId in vis_Rule
Table: vis_AND
Column1: AndId, Column2: IFId -> Foreign Key to IFId in vis_IF
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++
In the following stored proc, can we grab the RuleId of the first
SELECT statement and pass it to the next SELECT statement such that it
extracts only the desired rows? In other words, can we dynamically
assign values from one part of the SELECT statement to the next?
SELECT TOP 1
1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id],
NULL as [compare!4!id],
NULL as [compare!4!operator]
FROM vis_Rule r
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL,
NULL,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
NULL,
NULL,
NULL,
NULL,
if.IFId,
[and].AndId,
NULL,
NULL
FROM vis_AND [and], vis_IF if
where [and].IFId = if.IFId
FOR XML EXPLICIT
GO
Thanks in advance.
My apologies for the late reply, but I was on vacation the last couple of
weeks.
I am not quite sure what you try to acheive. Do you want to only provide the
nesting of the tree for a given ruleID?
In that case try:
CREATE Table vis_Rule (RuleId int,Name nvarchar(40), Priority int, Active
bit)
go
insert into vis_Rule values (1, N'r1', 1, 1)
insert into vis_Rule values (2, N'r2', 2, 1)
go
CREATE Table vis_IF (IFId int, RuleId int --> Foreign Key to RuleId in
vis_Rule
)
go
insert into vis_IF values (1, 1)
insert into vis_IF values (2, 1)
insert into vis_IF values (3, 1)
insert into vis_IF values (4, 2)
go
CREATE Table vis_AND (AndId int, IFId int --> Foreign Key to IFId in vis_IF
)
insert into vis_AND values (1, 1)
insert into vis_AND values (2, 1)
insert into vis_AND values (3, 2)
insert into vis_AND values (4, 2)
insert into vis_AND values (5, 3)
insert into vis_AND values (6, 4)
go
declare @.rid int
set @.rid = 1
SELECT TOP 1
1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id]
FROM vis_Rule r
where r.RuleId = @.rid
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
AND r.RuleId = @.rid
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
[if].RuleId,
NULL,
NULL,
NULL,
[if].IFId,
[and].AndId
FROM vis_AND [and], vis_IF [if]
where [and].IFId = [if].IFId
AND [if].RuleId = @.rid
ORDER BY [rule!1!Id], [if!2!id], Parent
FOR XML EXPLICIT
If you want it for all rules, try:
SELECT 1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id]
FROM vis_Rule r
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
[and].AndId
FROM vis_AND [and], vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
AND r.RuleId = [if].RuleId
AND [and].IFId = [if].IFId
ORDER BY [rule!1!Id], [if!2!id], Parent
FOR XML EXPLICIT
Best regards
Michael
<shamod@.gmail.com> wrote in message
news:1122527565.227837.240190@.g43g2000cwa.googlegr oups.com...
> Hi guys:
> I'm in an urgent need to know if we can dynamically alter values in
> querying tables using FOR XML EXPLICIT. In the following sample, I have
> 3 tabels involved vis_Rule, vis_IF, and vis_AND.
> Table Structure:
> Table: vis_Rule
> Column1: RuleId, Column2: Name, Column3: Priority, Column4: Active
> Table: vis_IF
> Column1: IFId, Column2: RuleId -> Foreign Key to RuleId in vis_Rule
> Table: vis_AND
> Column1: AndId, Column2: IFId -> Foreign Key to IFId in vis_IF
> ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++
> In the following stored proc, can we grab the RuleId of the first
> SELECT statement and pass it to the next SELECT statement such that it
> extracts only the desired rows? In other words, can we dynamically
> assign values from one part of the SELECT statement to the next?
> SELECT TOP 1
> 1 AS Tag,
> NULL AS Parent,
> r.RuleId as [rule!1!Id],
> r.Name as [rule!1!name],
> r.Priority as [rule!1!priority],
> r.Active as [rule!1!active],
> NULL as [if!2!id],
> NULL as [and!3!id],
> NULL as [compare!4!id],
> NULL as [compare!4!operator]
> FROM vis_Rule r
> UNION ALL
> SELECT 2 AS Tag,
> 1 AS Parent,
> r.RuleId,
> NULL,
> NULL,
> NULL,
> [if].IFId,
> NULL,
> NULL,
> NULL
> FROM vis_IF [if], vis_Rule r
> WHERE [if].RuleId = r.RuleId
> UNION ALL
> SELECT 3 AS Tag,
> 2 AS Parent,
> NULL,
> NULL,
> NULL,
> NULL,
> if.IFId,
> [and].AndId,
> NULL,
> NULL
> FROM vis_AND [and], vis_IF if
> where [and].IFId = if.IFId
> FOR XML EXPLICIT
> GO
> Thanks in advance.
>

For Each Loop Failure when some values in recordset are NULL

I have a for each loop container that is performing various tasks as it loops through a record set. Some of the values from the recordset are NULL at times and this causes the FELC to fail because it is unable to map the variables that have NULL values.

Is there something I can set to have it accept the NULLS or something I can change about the variables themselves. This is valid data that still needs to be processed. There are other attributes that have data in the record.

Here are samples of my error messages:

Error: ForEach Variable Mapping number 19 to variable "User::varTransactionReference" cannot be applied.

Error: ForEach Variable Mapping number 20 to variable "User::varFlowStartDate" cannot be applied.

Error: ForEach Variable Mapping number 26 to variable "User::varCancelFee" cannot be applied.

Thanks in advance.

SK

SK1000,

The For Each control is weak. There are much better controls

that you can use to process records. Here is 2

web pages that discuss different techniques using different

controls.

This is my web site
http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

This Jamie

Thompson's cool blog.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks,

Greg Van

Mullem

|||

Thanks, Greg. I will look at both options.

SK

Footer with dynamic values

In my report I need footer with fixed lenght anchored to a bottom. The
problem is what I need some dynamic fields (report values) in footer. How
can I manage this?I found answer by myself. :)
Who interested in problem read
http://msdn2.microsoft.com/en-us/library/ms159677
"gNM" <gedukas99@.hotmail.com> wrote in message
news:eaN8qB80FHA.3756@.tk2msftngp13.phx.gbl...
> In my report I need footer with fixed lenght anchored to a bottom. The
> problem is what I need some dynamic fields (report values) in footer. How
> can I manage this?
>