I'm the new guy and I could use a hand. Any insight would be appreciated.
Basically my task is to create a dynamic way to merge columns from multiple rows. Way the table is set up data is imported and one entry may be up to 3 rows one column from each row can be merged to form a long description, I would like to create a view that would allow you to dynamically query this data and have the description be merged in the result set.
row1 x y z
row2 x b z
row3 x m z
results should look like :: x, (y + b + m) , z
Thank you in advance for any help you can provide!numbers? you can SUM() them, but description? can't be done
you'll need to write some sort of script, either in the application that calls the database, or mayhaps in a stored proc that can cursor through the rows|||numbers? you can SUM() them, but description? can't be done
you'll need to write some sort of script, either in the application that calls the database, or mayhaps in a stored proc that can cursor through the rows
Unfortunately the data is all of char type. :confused:|||Any suggestions of how to go about doing this using a procedure?|||http://www.dbforums.com/t1038027.html|||Thanks for the link...I can't say I totally understand the function though.
I hate to ask this, and sound like an idiot, but can anyone give me the english break down?|||Cut and paste this code in to Query Analyzer...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40520
Showing posts with label basically. Show all posts
Showing posts with label basically. Show all posts
Tuesday, March 27, 2012
Monday, March 26, 2012
Dynamic Properties Task in DTS 2000, need to convert it to SSIS
I have a Dynamic propeties task in dts 2000 that process/executes a global variable.
The global variable basically executes a bat file.
How do i set this up in ssis. The migration failed to properly convert this task.
Please help.
Thank you.
To execute a .bat file you would use the Execute Process task.|||I don't think I can use the process task because I have a global variable which gets set in a previous task. It is via this global variable that a bat file is called that copies a file from one location to another. The issue is that how do I execute the global variable. In dts sql 2000, the dynamice properties task is used. but in ssis that does not work. the process task does not allow you to execute/process aglobal variable....
hope I was able to explain better. Any help is appreciated.
Thanks
|||You'll have to use expressions on the Execute Process Task. You'll likely have to create two variables based off of your global variable first, though. That is, the execute process takes two arguments at a minimum, the executable (cmd.exe perhaps) and its arguments (your bat file). Then, just pass in the two new variables into the appropriate expressions (Executable & Arguments)|||Thanks Phil,
I understand what you said, but not quite sure how to do it...
my global varaible is called gv_commandline
the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt
(batch file) (1st arg) (2nd arg)
basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)
For instance, how do I set the two new variables with values from my global variable.
How do I set the expressions
Thanks in advance.
Jinita
|||
Jain wrote:
Thanks Phil,
I understand what you said, but not quite sure how to do it...
my global varaible is called gv_commandline
the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt
(batch file) (1st arg) (2nd arg)
basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)
Thanks in advance.
Jinita
Actually, just try putting your global variable in the expression for Argument. Right click on the Execute Process Task and select properties. Find the expression parameter and click on the "...". Find "Arguments" and in that box, just drag your global variable to it. Then, click out of that and double click on the execute process task to configure it. For the executable, type in: c:\windows\system32\cmd.exe|||
Thanks Phil,
That was awesome. Looks like it worked. the task seems to work, I will test the complete package run just to make sure everything works fine.
Great. Thank you so much.
Have a great weekend :
Jinita
|||Hi Phil,
Apologise for reopening this issue, but I am having problems with the task. I have done what you suggested before, but when I try to execute that process task, it comes up with the cmd.exe window waiting for the command or argument. Shouldn't it take the argument and execute the whole thing. Please help.
Thank you.
|||I am still running into the same problem. Why does the command prompt come up. I am expecting it to execute the process task since i have already provided it with an executable and arguments.
Please advice.
Thanks in advance.
Dynamic Properties Task in DTS 2000, need to convert it to SSIS
I have a Dynamic propeties task in dts 2000 that process/executes a global variable.
The global variable basically executes a bat file.
How do i set this up in ssis. The migration failed to properly convert this task.
Please help.
Thank you.
To execute a .bat file you would use the Execute Process task.|||I don't think I can use the process task because I have a global variable which gets set in a previous task. It is via this global variable that a bat file is called that copies a file from one location to another. The issue is that how do I execute the global variable. In dts sql 2000, the dynamice properties task is used. but in ssis that does not work. the process task does not allow you to execute/process aglobal variable....
hope I was able to explain better. Any help is appreciated.
Thanks
|||You'll have to use expressions on the Execute Process Task. You'll likely have to create two variables based off of your global variable first, though. That is, the execute process takes two arguments at a minimum, the executable (cmd.exe perhaps) and its arguments (your bat file). Then, just pass in the two new variables into the appropriate expressions (Executable & Arguments)|||Thanks Phil,
I understand what you said, but not quite sure how to do it...
my global varaible is called gv_commandline
the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt
(batch file) (1st arg) (2nd arg)
basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)
For instance, how do I set the two new variables with values from my global variable.
How do I set the expressions
Thanks in advance.
Jinita
|||
Thanks Phil,
I understand what you said, but not quite sure how to do it...
my global varaible is called gv_commandline
the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt
(batch file) (1st arg) (2nd arg)
basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)
Thanks in advance.
Jinita
Actually, just try putting your global variable in the expression for Argument. Right click on the Execute Process Task and select properties. Find the expression parameter and click on the "...". Find "Arguments" and in that box, just drag your global variable to it. Then, click out of that and double click on the execute process task to configure it. For the executable, type in: c:\windows\system32\cmd.exe|||
Thanks Phil,
That was awesome. Looks like it worked. the task seems to work, I will test the complete package run just to make sure everything works fine.
Great. Thank you so much.
Have a great weekend :
Jinita
|||Hi Phil,
Apologise for reopening this issue, but I am having problems with the task. I have done what you suggested before, but when I try to execute that process task, it comes up with the cmd.exe window waiting for the command or argument. Shouldn't it take the argument and execute the whole thing. Please help.
Thank you.
|||I am still running into the same problem. Why does the command prompt come up. I am expecting it to execute the process task since i have already provided it with an executable and arguments.
Please advice.
Thanks in advance.
Dynamic Properties Task in DTS 2000, need to convert it to SSIS
I have a Dynamic propeties task in dts 2000 that process/executes a global variable.
The global variable basically executes a bat file.
How do i set this up in ssis. The migration failed to properly convert this task.
Please help.
Thank you.
To execute a .bat file you would use the Execute Process task.|||I don't think I can use the process task because I have a global variable which gets set in a previous task. It is via this global variable that a bat file is called that copies a file from one location to another. The issue is that how do I execute the global variable. In dts sql 2000, the dynamice properties task is used. but in ssis that does not work. the process task does not allow you to execute/process aglobal variable....
hope I was able to explain better. Any help is appreciated.
Thanks
|||You'll have to use expressions on the Execute Process Task. You'll likely have to create two variables based off of your global variable first, though. That is, the execute process takes two arguments at a minimum, the executable (cmd.exe perhaps) and its arguments (your bat file). Then, just pass in the two new variables into the appropriate expressions (Executable & Arguments)|||Thanks Phil,
I understand what you said, but not quite sure how to do it...
my global varaible is called gv_commandline
the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt
(batch file) (1st arg) (2nd arg)
basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)
For instance, how do I set the two new variables with values from my global variable.
How do I set the expressions
Thanks in advance.
Jinita
|||
Thanks Phil,
I understand what you said, but not quite sure how to do it...
my global varaible is called gv_commandline
the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt
(batch file) (1st arg) (2nd arg)
basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)
Thanks in advance.
Jinita
Actually, just try putting your global variable in the expression for Argument. Right click on the Execute Process Task and select properties. Find the expression parameter and click on the "...". Find "Arguments" and in that box, just drag your global variable to it. Then, click out of that and double click on the execute process task to configure it. For the executable, type in: c:\windows\system32\cmd.exe|||
Thanks Phil,
That was awesome. Looks like it worked. the task seems to work, I will test the complete package run just to make sure everything works fine.
Great. Thank you so much.
Have a great weekend :
Jinita
|||Hi Phil,
Apologise for reopening this issue, but I am having problems with the task. I have done what you suggested before, but when I try to execute that process task, it comes up with the cmd.exe window waiting for the command or argument. Shouldn't it take the argument and execute the whole thing. Please help.
Thank you.
|||I am still running into the same problem. Why does the command prompt come up. I am expecting it to execute the process task since i have already provided it with an executable and arguments.
Please advice.
Thanks in advance.
sqlThursday, March 22, 2012
Dynamic Parameter List
Is it possible to have a dynamic number of parameters? Basically I have a
vertical table that will contain a number of records. As records are added
to this table, they will need to be passed to a stored procedure. So say I
have this structure:
SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
@.dynamic_param2, etc. etc.
Tbl_Dynamic_Params
ID
1 Nationality
2 Net Worth
3 Hair Color
4 Shoe Size
So I want to pass:
SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
@.Net_Worth, @.HairColor, @.ShoeSize.
Is this possible without using dynamic SQL?"James" <neg@.tory.com> wrote in message
news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
> Is it possible to have a dynamic number of parameters? Basically I have a
> vertical table that will contain a number of records. As records are
> added to this table, they will need to be passed to a stored procedure.
> So say I have this structure:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
> @.dynamic_param2, etc. etc.
> Tbl_Dynamic_Params
> ID
> 1 Nationality
> 2 Net Worth
> 3 Hair Color
> 4 Shoe Size
> So I want to pass:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
> @.Net_Worth, @.HairColor, @.ShoeSize.
> Is this possible without using dynamic SQL?
>
You can have *optional* parameters in a proc, yes. Just specify a default
value for those parameters. For example:
CREATE PROC usp_proc
@.p1 INT = NULL,
@.p2 INT = NULL
AS ...
Both @.p1 and @.p2 are optional and will default to null if they are not
specified. Obviously your parameter *names* must be known at runtime
otherwise you couldn't write any code that used those parameters could you?
Important: Do not use "sp_" as a prefix for your procs. "sp_" is the name
used for system procs and has a special meaning that will cause unwanted
side-effects to your code.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I don't actually use sp as a prefix, it was merely a visual cue. I
understand optional parameters, but I want the list of parameters to be
dynamic, not just the arguments...that's the crux of the problem. I don't
think there's a really good solution to this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uX5l3nC5HHA.5724@.TK2MSFTNGP05.phx.gbl...
> "James" <neg@.tory.com> wrote in message
> news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
>> Is it possible to have a dynamic number of parameters? Basically I have
>> a vertical table that will contain a number of records. As records are
>> added to this table, they will need to be passed to a stored procedure.
>> So say I have this structure:
>> SP_Update_Contact @.first_name, @.last_name, @.city, @.state,
>> @.dynamic_param1, @.dynamic_param2, etc. etc.
>> Tbl_Dynamic_Params
>> ID
>> 1 Nationality
>> 2 Net Worth
>> 3 Hair Color
>> 4 Shoe Size
>> So I want to pass:
>> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
>> @.Net_Worth, @.HairColor, @.ShoeSize.
>> Is this possible without using dynamic SQL?
> You can have *optional* parameters in a proc, yes. Just specify a default
> value for those parameters. For example:
>
> CREATE PROC usp_proc
> @.p1 INT = NULL,
> @.p2 INT = NULL
> AS ...
> Both @.p1 and @.p2 are optional and will default to null if they are not
> specified. Obviously your parameter *names* must be known at runtime
> otherwise you couldn't write any code that used those parameters could
> you?
> Important: Do not use "sp_" as a prefix for your procs. "sp_" is the name
> used for system procs and has a special meaning that will cause unwanted
> side-effects to your code.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||"James" <neg@.tory.com> wrote in message
news:eK3xtqC5HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I don't actually use sp as a prefix, it was merely a visual cue. I
>understand optional parameters, but I want the list of parameters to be
>dynamic, not just the arguments...that's the crux of the problem. I don't
>think there's a really good solution to this.
>
I don't understand what you mean when you say you want the "list of
parameters to be dynamic". Parameters have names and types. If you don't
know the names and types in advance then how could you possibly write a proc
that made use of them - even assuming it was possible to pass them as
parameters?
Maybe what you want is an array. SQL Server has a data structure that is
much more powerful than an array: a table! :-)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> ... but I want the list of parameters to be dynamic, not just the
>> arguments...that's the crux of the problem.
The crux of the problem has to do with the poor design scheme you use to
represent various attributes as values. If you have a static set of
properties that you have to manage, treat them as columns rather than values
in a column. The right answer is that you should reconsider the logical
design. Mixing up data with metadata is always a recipe for messy and
complex solutions.
--
Anith|||You're preaching to the choir. This isn't my design. Let me rewind. A
colleague approach me with this problem.
Contacts Table:
ID First_Name Last_Name ... ...
Pretty basic. Now there is additional data that needs to be stored. I.E.
Shoe Size, Hat Size, and various other data. However, not every record will
have all of these fields. Also, fields need to be added on the fly with the
lowest amount of maintenance possible.
Performance is a huge consideration. He has approximately 2 million records
in the Contacts table and say, 10 fields +/-. He believes that performance
will be better if he has a flat table that is a 1-to-1 with this table that
stores the extra columns...as opposed to a more normalized alternative that
they have in place now which CRAWLS.
Should I talk him out of this redesign, encourage it, or suggest an
alternative? If so, that begs the question...what alternative?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23rOPW9C5HHA.4964@.TK2MSFTNGP06.phx.gbl...
>> ... but I want the list of parameters to be dynamic, not just the
>> arguments...that's the crux of the problem.
> The crux of the problem has to do with the poor design scheme you use to
> represent various attributes as values. If you have a static set of
> properties that you have to manage, treat them as columns rather than
> values in a column. The right answer is that you should reconsider the
> logical design. Mixing up data with metadata is always a recipe for messy
> and complex solutions.
> --
> Anith
>|||"James" <neg@.tory.com> wrote in message
news:eLGgUPD5HHA.5316@.TK2MSFTNGP04.phx.gbl...
> You're preaching to the choir. This isn't my design. Let me rewind. A
> colleague approach me with this problem.
> Contacts Table:
> ID First_Name Last_Name ... ...
> Pretty basic. Now there is additional data that needs to be stored. I.E.
> Shoe Size, Hat Size, and various other data. However, not every record
> will have all of these fields. Also, fields need to be added on the fly
> with the lowest amount of maintenance possible.
> Performance is a huge consideration. He has approximately 2 million
> records in the Contacts table and say, 10 fields +/-. He believes that
> performance will be better if he has a flat table that is a 1-to-1 with
> this table that stores the extra columns...as opposed to a more normalized
> alternative that they have in place now which CRAWLS.
> Should I talk him out of this redesign, encourage it, or suggest an
> alternative? If so, that begs the question...what alternative?
>
Two million rows is not large and should be well within the capabilities of
even an entry level server. If performance is inadequate then start looking
at indexing, query plans, statistics and procedure design.
As for the logical model, a normalized design is the right place to start.
Nothing you have said suggests any need for a change from that.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:elfSovC5HHA.3400@.TK2MSFTNGP03.phx.gbl...
>.
> Maybe what you want is an array. SQL Server has a data structure that is
> much more powerful than an array: a table! :-)
>.
This is a disturbing statement from someone who purports to
understand a strong typed system. I assume its rationale
is not intellectual. Shall I pull out the sql couch and
start the examination? :-)
www.beyondsql.blogspot.com|||You mean like printf. I seriously doubt it considering the hoops C has to go
through to make it work.
How about name/value pairs passed into one large varchar?
set @.paramerters = 'first_name='+@.first_name+';last_name='+@.last_name
create procedure sp_update_contact @.parameters
as
declare @.parameters varchar(max)
loop through the string looking for the ; delimiter, then split the
name/value pair and put it in a table variable:
declare @.MyArray TABLE (
name varchar(20)
value varchar(20)
)
Then do as you please.
Since the name portion had to corospond to a column in the table, you better
have some decent error checking an support for an error return.
"James" <neg@.tory.com> wrote in message
news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
> Is it possible to have a dynamic number of parameters? Basically I have a
> vertical table that will contain a number of records. As records are
> added to this table, they will need to be passed to a stored procedure.
> So say I have this structure:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
> @.dynamic_param2, etc. etc.
> Tbl_Dynamic_Params
> ID
> 1 Nationality
> 2 Net Worth
> 3 Hair Color
> 4 Shoe Size
> So I want to pass:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
> @.Net_Worth, @.HairColor, @.ShoeSize.
> Is this possible without using dynamic SQL?
>sql
vertical table that will contain a number of records. As records are added
to this table, they will need to be passed to a stored procedure. So say I
have this structure:
SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
@.dynamic_param2, etc. etc.
Tbl_Dynamic_Params
ID
1 Nationality
2 Net Worth
3 Hair Color
4 Shoe Size
So I want to pass:
SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
@.Net_Worth, @.HairColor, @.ShoeSize.
Is this possible without using dynamic SQL?"James" <neg@.tory.com> wrote in message
news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
> Is it possible to have a dynamic number of parameters? Basically I have a
> vertical table that will contain a number of records. As records are
> added to this table, they will need to be passed to a stored procedure.
> So say I have this structure:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
> @.dynamic_param2, etc. etc.
> Tbl_Dynamic_Params
> ID
> 1 Nationality
> 2 Net Worth
> 3 Hair Color
> 4 Shoe Size
> So I want to pass:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
> @.Net_Worth, @.HairColor, @.ShoeSize.
> Is this possible without using dynamic SQL?
>
You can have *optional* parameters in a proc, yes. Just specify a default
value for those parameters. For example:
CREATE PROC usp_proc
@.p1 INT = NULL,
@.p2 INT = NULL
AS ...
Both @.p1 and @.p2 are optional and will default to null if they are not
specified. Obviously your parameter *names* must be known at runtime
otherwise you couldn't write any code that used those parameters could you?
Important: Do not use "sp_" as a prefix for your procs. "sp_" is the name
used for system procs and has a special meaning that will cause unwanted
side-effects to your code.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I don't actually use sp as a prefix, it was merely a visual cue. I
understand optional parameters, but I want the list of parameters to be
dynamic, not just the arguments...that's the crux of the problem. I don't
think there's a really good solution to this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uX5l3nC5HHA.5724@.TK2MSFTNGP05.phx.gbl...
> "James" <neg@.tory.com> wrote in message
> news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
>> Is it possible to have a dynamic number of parameters? Basically I have
>> a vertical table that will contain a number of records. As records are
>> added to this table, they will need to be passed to a stored procedure.
>> So say I have this structure:
>> SP_Update_Contact @.first_name, @.last_name, @.city, @.state,
>> @.dynamic_param1, @.dynamic_param2, etc. etc.
>> Tbl_Dynamic_Params
>> ID
>> 1 Nationality
>> 2 Net Worth
>> 3 Hair Color
>> 4 Shoe Size
>> So I want to pass:
>> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
>> @.Net_Worth, @.HairColor, @.ShoeSize.
>> Is this possible without using dynamic SQL?
> You can have *optional* parameters in a proc, yes. Just specify a default
> value for those parameters. For example:
>
> CREATE PROC usp_proc
> @.p1 INT = NULL,
> @.p2 INT = NULL
> AS ...
> Both @.p1 and @.p2 are optional and will default to null if they are not
> specified. Obviously your parameter *names* must be known at runtime
> otherwise you couldn't write any code that used those parameters could
> you?
> Important: Do not use "sp_" as a prefix for your procs. "sp_" is the name
> used for system procs and has a special meaning that will cause unwanted
> side-effects to your code.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||"James" <neg@.tory.com> wrote in message
news:eK3xtqC5HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I don't actually use sp as a prefix, it was merely a visual cue. I
>understand optional parameters, but I want the list of parameters to be
>dynamic, not just the arguments...that's the crux of the problem. I don't
>think there's a really good solution to this.
>
I don't understand what you mean when you say you want the "list of
parameters to be dynamic". Parameters have names and types. If you don't
know the names and types in advance then how could you possibly write a proc
that made use of them - even assuming it was possible to pass them as
parameters?
Maybe what you want is an array. SQL Server has a data structure that is
much more powerful than an array: a table! :-)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> ... but I want the list of parameters to be dynamic, not just the
>> arguments...that's the crux of the problem.
The crux of the problem has to do with the poor design scheme you use to
represent various attributes as values. If you have a static set of
properties that you have to manage, treat them as columns rather than values
in a column. The right answer is that you should reconsider the logical
design. Mixing up data with metadata is always a recipe for messy and
complex solutions.
--
Anith|||You're preaching to the choir. This isn't my design. Let me rewind. A
colleague approach me with this problem.
Contacts Table:
ID First_Name Last_Name ... ...
Pretty basic. Now there is additional data that needs to be stored. I.E.
Shoe Size, Hat Size, and various other data. However, not every record will
have all of these fields. Also, fields need to be added on the fly with the
lowest amount of maintenance possible.
Performance is a huge consideration. He has approximately 2 million records
in the Contacts table and say, 10 fields +/-. He believes that performance
will be better if he has a flat table that is a 1-to-1 with this table that
stores the extra columns...as opposed to a more normalized alternative that
they have in place now which CRAWLS.
Should I talk him out of this redesign, encourage it, or suggest an
alternative? If so, that begs the question...what alternative?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23rOPW9C5HHA.4964@.TK2MSFTNGP06.phx.gbl...
>> ... but I want the list of parameters to be dynamic, not just the
>> arguments...that's the crux of the problem.
> The crux of the problem has to do with the poor design scheme you use to
> represent various attributes as values. If you have a static set of
> properties that you have to manage, treat them as columns rather than
> values in a column. The right answer is that you should reconsider the
> logical design. Mixing up data with metadata is always a recipe for messy
> and complex solutions.
> --
> Anith
>|||"James" <neg@.tory.com> wrote in message
news:eLGgUPD5HHA.5316@.TK2MSFTNGP04.phx.gbl...
> You're preaching to the choir. This isn't my design. Let me rewind. A
> colleague approach me with this problem.
> Contacts Table:
> ID First_Name Last_Name ... ...
> Pretty basic. Now there is additional data that needs to be stored. I.E.
> Shoe Size, Hat Size, and various other data. However, not every record
> will have all of these fields. Also, fields need to be added on the fly
> with the lowest amount of maintenance possible.
> Performance is a huge consideration. He has approximately 2 million
> records in the Contacts table and say, 10 fields +/-. He believes that
> performance will be better if he has a flat table that is a 1-to-1 with
> this table that stores the extra columns...as opposed to a more normalized
> alternative that they have in place now which CRAWLS.
> Should I talk him out of this redesign, encourage it, or suggest an
> alternative? If so, that begs the question...what alternative?
>
Two million rows is not large and should be well within the capabilities of
even an entry level server. If performance is inadequate then start looking
at indexing, query plans, statistics and procedure design.
As for the logical model, a normalized design is the right place to start.
Nothing you have said suggests any need for a change from that.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:elfSovC5HHA.3400@.TK2MSFTNGP03.phx.gbl...
>.
> Maybe what you want is an array. SQL Server has a data structure that is
> much more powerful than an array: a table! :-)
>.
This is a disturbing statement from someone who purports to
understand a strong typed system. I assume its rationale
is not intellectual. Shall I pull out the sql couch and
start the examination? :-)
www.beyondsql.blogspot.com|||You mean like printf. I seriously doubt it considering the hoops C has to go
through to make it work.
How about name/value pairs passed into one large varchar?
set @.paramerters = 'first_name='+@.first_name+';last_name='+@.last_name
create procedure sp_update_contact @.parameters
as
declare @.parameters varchar(max)
loop through the string looking for the ; delimiter, then split the
name/value pair and put it in a table variable:
declare @.MyArray TABLE (
name varchar(20)
value varchar(20)
)
Then do as you please.
Since the name portion had to corospond to a column in the table, you better
have some decent error checking an support for an error return.
"James" <neg@.tory.com> wrote in message
news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
> Is it possible to have a dynamic number of parameters? Basically I have a
> vertical table that will contain a number of records. As records are
> added to this table, they will need to be passed to a stored procedure.
> So say I have this structure:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
> @.dynamic_param2, etc. etc.
> Tbl_Dynamic_Params
> ID
> 1 Nationality
> 2 Net Worth
> 3 Hair Color
> 4 Shoe Size
> So I want to pass:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
> @.Net_Worth, @.HairColor, @.ShoeSize.
> Is this possible without using dynamic SQL?
>sql
Dynamic package configurations
I have a package that will be run by many people, basically that take a flat file from one format to another format. The variables I'd need to change are @.originalFilePath and @.destinationFilePath.
I'm looking at package configurations now, and am wondering what I'm missing. It seems like the configuration settings are static, with an xml file or a sql server table being my most viable options.
What I need is for multiple users to be able to execute the same package with different parameters at the same time. Can anyone guide me in the right direction? I know that through ASP, I could generate XML docs on the fly, but I want to make sure I'm using the best method. What's the best way of going about dynamic configs?
Take a look at DTEXEC and the /SET option. Using that, you can call your pacakge and use SET to override the values of @.originalFilePath and @.destinationFilePath. No configuration file required. It does mean passing the values each time, but given your situation it sounds like that will be the best solution.|||Interesting... I'm looking into it a bit, thanks for that John. How about options available through vb.net 2003?
I started tinkering around with it, but couldn't get the Microsoft.SqlServer.ManagedDTS.dll to be added to the references. (side question... does it require 2005 to reference this assembly?)
|||
Interesting... I'm looking into it a bit, thanks for that John. How about options available through vb.net 2003? I started tinkering around with it, but couldn't get the Microsoft.SqlServer.ManagedDTS.dll to be added to the references. (side question... does it require 2005 to reference this assembly?)
Strictly speaking, .Net 2.0 is required.|||any ideas why I get the error "A reference to 'C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll' could not be added. This is not a valid assembly or COM component."?
I have Visual Studio 2003, and .NET 1.1 and 2.0 framework installed on my local machine.
|||Does any of this information help?
http://blogs.msdn.com/michen/default.aspx|||it does help, yeah. the part that says:
Drawbacks: Obviously this is local execution - you need to install SSIS on same machine where your app runs. This method also can't be used from .NET 1.1 application, unless it is moved to .NET 2.0 (which should be very easy to do, and in my experience improves the performance as well).
So I guess I need VS 2005 in order to use the object model for SSIS?
|||
it does help, yeah. the part that says: Drawbacks: Obviously this is local execution - you need to install SSIS on same machine where your app runs.
which means you need a license on each machine running the server. were you aware of that?
Sunday, March 11, 2012
Dynamic FLOWR XQuery into an xml variable
Hello.
I'm trying to do something I haven't seen anywhere, and have been
trying everything but no joy. Basically, my XML is in a variable, and
I'm querying that variable to filter it further. The problem is that
these filters are dynamic. I want to keep the results as an xml
variable because I want to work with them further after this query (I
will do an xpath query so I can use position() and return a page). I'd
really like to stay away from using a tmp table for this.
See the code inline for a few of the things I've tried. I appreciate
any help on this...
Thanks very much in advance for anyone taking the time to help.
Relevant Code with sample xml filling the var
This should be ready to run (and break on the dynamic attempts)
Move the close comments (*/) around to try one method at a time
---
DECLARE @.ItemXML xml
SET @.ItemXML = '
<itemdata>
<item>
<rank>558</rank>
<itemid>11111111</itemid>
<catid>1</catid>
<pid>11</pid>
<ctid>1</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 1</title>
<published>2006-04-05T15:52:17</published>
<provider>foo provider #1</provider>
</item>
<item>
<rank>558</rank>
<itemid>22222222</itemid>
<catid>22</catid>
<pid>50</pid>
<ctid>2</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 2</title>
<published>2006-04-05T15:52:17</published>
<provider>foo provider #2</provider>
</item>
<item>
<rank>558</rank>
<itemid>333333333</itemid>
<catid>33</catid>
<pid>50</pid>
<ctid>3</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 3</title>
<published>2006-04-05T15:52:17</published>
<provider>foo provider #3</provider>
</item>
</itemdata>'
DECLARE @.CatID int, @.ProviderID int, @.CTID int
-- JUST SET 1 VAR FOR NOW, BUT THIS IS BUILT TO ALLOW MULTIPLE FILTERS
SET @.CTID = 2
DECLARE @.ItemStmt varchar(500), @.XQuery varchar(300), @.WhereStmt
varchar(200)
SET @.WhereStmt = 'where 1 = 1'
IF @.CatID IS NOT NULL
SET @.WhereStmt = @.WhereStmt + ' and $i/catid[1] =
'+CONVERT(varchar(4),@.CatID)
IF @.ProviderID IS NOT NULL
SET @.WhereStmt = @.WhereStmt + ' and $i/pid[1] =
'+CONVERT(varchar(4),@.ProviderID)
IF @.CTID IS NOT NULL
SET @.WhereStmt = @.WhereStmt + ' and $i/ctid[1] =
'+CONVERT(varchar(4),@.CTID)
/*
Base Query with no dynamic vars... this works
*/
SET @.ItemXML = @.ItemXML.query('<itemdata>{
for $i in itemdata[1]/item
where 1 = 1 and $i/ctid[1] = 2
return $i
}</itemdata>
')
SELECT 'Attempt #1: Works',@.ItemXML
/*
Attempt #2... sort of like mrorke at
http://blogs.msdn.com/mrorke/archiv.../24/484237.aspx
EXEC the entire SET @.Var statement.
This results in "Must declare the scalar variable "@.ItemXML"."
-- move close comment here:
SET @.XQuery = '<itemdata>{
for $i in itemdata[1]/item
'+@.WhereStmt+'
return $i
}
</itemdata>'
SET @.XQuery = 'SET @.ItemXML = @.ItemXML.query('''+@.XQuery+''')'
EXEC(@.XQuery)
SELECT 'Attempt #2: Breaks', @.ItemXML
*/
/*
Attempt #3... Try a sql:variable inline
Result: XQuery [query()]: Syntax error near 'sql', expected 'where',
'(stable) order by' or 'return'.
-- move close comment here:
SET @.ItemXML = @.ItemXML.query('<itemdata>{
for $i in itemdata[1]/item
sql:variable("@.WhereStmt")
return $i
}
</itemdata>')
SELECT 'Attempt #3: Breaks', @.ItemXML
*/Rather than using EXEC(@.XQuery), try using this
EXEC sp_executeSQL @.XQuery,N'@.ItemXML XML OUTPUT',@.ItemXML OUTPUT
You'll have to declare @.XQuery as nvarchar instead of varchar|||Markc. Thank you very much for your response. That did exactly what I
needed it to do.
One problem I'm seeing now is in my next step where I apply an xpath to
the @.ItemXML just to do the pagination. It seems that xpath step then
accounts for 85% of my query cost. I'm trying another approach just to
be sure I've covered all my bases and can get the most performant
solution, and I'm running into trouble because I'm still learning the
xml datatype & some other SQL 2005 features. Anyway, I though I'd try a
query against the xml variable, while also joining to my lookup tables
and creating a rownumber for pagination. I've tried several different
syntax with this, but it keeps breaking with "Invalid object name 'R'",
so if anyone can see what I'm doing wrong, again, I'd greatly
appreciate it.
SELECT ROW_NUMBER() OVER (ORDER BY rank DESC) AS RowNumber,
i.value('rank','int'),
i.value('itemid','int'),
i.value('catid','int'),
dbo.c.vch_categorytype AS cat,
i.value('pid','int'),
dbo.p.vch_providername_public AS provider,
i.value('ctid','int'),
i.value('link','varchar(300)'),
i.value('thumburl','varchar(300)'),
i.value('title','varchar(500)'),
i.value('published','datetime')
FROM R cross apply @.ItemXML.nodes('itemdata') R(i)
JOIN Categories_LU c ON R.i.value('catid','int') = c.i_category_id
JOIN Providers_LU p ON R.i.value('pid','int') = p.i_provider_id
WHERE RowNumber BETWEEN 1 AND 20 -- assume page 1, items 1-20
AND R.i.value('ctid','int') = 1 -- this would eventually be a dynamic
where clause|||I think
FROM R cross apply @.ItemXML.nodes('itemdata') R(i)
should be
FROM @.ItemXML.nodes('itemdata') R(i)
However, I believe there are other problems here as well. Suggest you
post the DDL for the two tables and some sample data.
Regards
Mark|||Sorry... I know @.ItemXML.nodes('itemdata') won't return the nodes... I
was thrashing around trying different things. Here are some other ways
I've tried to get this...
FROM R cross apply @.ItemXML.nodes('itemdata/item') R(i)
FROM FooTblNm cross apply @.ItemXML.nodes('itemdata/item') R(i)
... also have mixed up R.i.value and i.value... Nothing made a
difference. Always came back with the table ("R", "FooTblNm") being an
invalid object. Sure, I know answer #1 is I need to read a book so I
don't have to guess on syntax. :-)
Thanks again,
STA|||Here's a complete query with tmp table data...
Again, thanks for your help.
----
--
DECLARE @.ItemXML xml
SET @.ItemXML = '
<itemdata>
<item>
<rank>558</rank>
<itemid>11111111</itemid>
<catid>1</catid>
<pid>11</pid>
<ctid>1</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 1</title>
<published>2006-04-05T15:52:17</published>
</item>
<item>
<rank>558</rank>
<itemid>22222222</itemid>
<catid>22</catid>
<pid>50</pid>
<ctid>2</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 2</title>
<published>2006-04-05T15:52:17</published>
</item>
<item>
<rank>558</rank>
<itemid>333333333</itemid>
<catid>33</catid>
<pid>50</pid>
<ctid>3</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 3</title>
<published>2006-04-05T15:52:17</published>
</item>
</itemdata>'
-- CHECK TO SEE IF #tmps ARE THERE ALREADY...
-- THE PROBABLY DIDN'T GET DROPPED BECAUSE THE QUERY BROKE
IF object_id('tempdb..#tmpCats') IS NULL
BEGIN
CREATE TABLE #tmpCats (i_category_id int, vch_category
varchar(100))
INSERT #tmpCats VALUES (1,'Category 1')
INSERT #tmpCats VALUES (2,'Category 2')
INSERT #tmpCats VALUES (3,'Category 3')
END
IF object_id('tempdb..#tmpProviders') IS NULL
BEGIN
CREATE TABLE #tmpProviders (i_provider_id int, vch_provider
varchar(100))
INSERT #tmpProviders VALUES (1,'Provider 1')
INSERT #tmpProviders VALUES (2,'Provider 2')
INSERT #tmpProviders VALUES (3,'Provider 3')
END
SELECT ROW_NUMBER() OVER (ORDER BY rank DESC) AS RowNumber,
i.value('rank','int'),
i.value('itemid','int'),
i.value('catid','int'),
c.vch_category AS cat,
i.value('pid','int'),
p.vch_provider AS provider,
i.value('ctid','int'),
i.value('link','varchar(300)'),
i.value('thumburl','varchar(300)'),
i.value('title','varchar(500)'),
i.value('published','datetime')
FROM R cross apply @.ItemXML.nodes('itemdata/item') R(i)
JOIN #tmpCats c ON R.i.value('catid','int') = c.i_category_id
JOIN #tmpProviders p ON R.i.value('pid','int') = p.i_provider_id
WHERE RowNumber BETWEEN 1 AND 20 -- assume page 1, items 1-20
AND R.i.value('ctid','int') = 1 -- eventually a dynamic where clause
DROP TABLE #tmpCats
DROP TABLE #tmpProviders|||This should help get you started
;
WITH
XMLNodes(RowNumber,rank,itemid,catid,cat
,pid,provider,ctid,link,thumburl,tit
le,published)
AS(
SELECT ROW_NUMBER() OVER (ORDER BY i.value('rank[1]','int') DESC) AS
RowNumber,
i.value('rank[1]','int'),
i.value('itemid[1]','int'),
i.value('catid[1]','int'),
c.vch_category,
i.value('pid[1]','int'),
p.vch_provider,
i.value('ctid[1]','int'),
i.value('link[1]','varchar(300)'),
i.value('thumburl[1]','varchar(300)'),
i.value('title[1]','varchar(500)'),
i.value('published[1]','datetime')
FROM @.ItemXML.nodes('/itemdata/item') R(i)
JOIN #tmpCats c ON R.i.value('catid[1]','int') = c.i_category_id
JOIN #tmpProviders p ON R.i.value('pid[1]','int') = p.i_provider_id
WHERE R.i.value('ctid[1]','int') = 1
)
SELECT RowNumber,
rank,
itemid,
catid,
cat,
pid,
ctid,
link,
thumburl,
title,
published
FROM XMLNodes
WHERE RowNumber BETWEEN 1 AND 20|||mark,
You absolutely rock! Thank you so much for your help on this.
STA
I'm trying to do something I haven't seen anywhere, and have been
trying everything but no joy. Basically, my XML is in a variable, and
I'm querying that variable to filter it further. The problem is that
these filters are dynamic. I want to keep the results as an xml
variable because I want to work with them further after this query (I
will do an xpath query so I can use position() and return a page). I'd
really like to stay away from using a tmp table for this.
See the code inline for a few of the things I've tried. I appreciate
any help on this...
Thanks very much in advance for anyone taking the time to help.
Relevant Code with sample xml filling the var
This should be ready to run (and break on the dynamic attempts)
Move the close comments (*/) around to try one method at a time
---
DECLARE @.ItemXML xml
SET @.ItemXML = '
<itemdata>
<item>
<rank>558</rank>
<itemid>11111111</itemid>
<catid>1</catid>
<pid>11</pid>
<ctid>1</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 1</title>
<published>2006-04-05T15:52:17</published>
<provider>foo provider #1</provider>
</item>
<item>
<rank>558</rank>
<itemid>22222222</itemid>
<catid>22</catid>
<pid>50</pid>
<ctid>2</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 2</title>
<published>2006-04-05T15:52:17</published>
<provider>foo provider #2</provider>
</item>
<item>
<rank>558</rank>
<itemid>333333333</itemid>
<catid>33</catid>
<pid>50</pid>
<ctid>3</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 3</title>
<published>2006-04-05T15:52:17</published>
<provider>foo provider #3</provider>
</item>
</itemdata>'
DECLARE @.CatID int, @.ProviderID int, @.CTID int
-- JUST SET 1 VAR FOR NOW, BUT THIS IS BUILT TO ALLOW MULTIPLE FILTERS
SET @.CTID = 2
DECLARE @.ItemStmt varchar(500), @.XQuery varchar(300), @.WhereStmt
varchar(200)
SET @.WhereStmt = 'where 1 = 1'
IF @.CatID IS NOT NULL
SET @.WhereStmt = @.WhereStmt + ' and $i/catid[1] =
'+CONVERT(varchar(4),@.CatID)
IF @.ProviderID IS NOT NULL
SET @.WhereStmt = @.WhereStmt + ' and $i/pid[1] =
'+CONVERT(varchar(4),@.ProviderID)
IF @.CTID IS NOT NULL
SET @.WhereStmt = @.WhereStmt + ' and $i/ctid[1] =
'+CONVERT(varchar(4),@.CTID)
/*
Base Query with no dynamic vars... this works
*/
SET @.ItemXML = @.ItemXML.query('<itemdata>{
for $i in itemdata[1]/item
where 1 = 1 and $i/ctid[1] = 2
return $i
}</itemdata>
')
SELECT 'Attempt #1: Works',@.ItemXML
/*
Attempt #2... sort of like mrorke at
http://blogs.msdn.com/mrorke/archiv.../24/484237.aspx
EXEC the entire SET @.Var statement.
This results in "Must declare the scalar variable "@.ItemXML"."
-- move close comment here:
SET @.XQuery = '<itemdata>{
for $i in itemdata[1]/item
'+@.WhereStmt+'
return $i
}
</itemdata>'
SET @.XQuery = 'SET @.ItemXML = @.ItemXML.query('''+@.XQuery+''')'
EXEC(@.XQuery)
SELECT 'Attempt #2: Breaks', @.ItemXML
*/
/*
Attempt #3... Try a sql:variable inline
Result: XQuery [query()]: Syntax error near 'sql', expected 'where',
'(stable) order by' or 'return'.
-- move close comment here:
SET @.ItemXML = @.ItemXML.query('<itemdata>{
for $i in itemdata[1]/item
sql:variable("@.WhereStmt")
return $i
}
</itemdata>')
SELECT 'Attempt #3: Breaks', @.ItemXML
*/Rather than using EXEC(@.XQuery), try using this
EXEC sp_executeSQL @.XQuery,N'@.ItemXML XML OUTPUT',@.ItemXML OUTPUT
You'll have to declare @.XQuery as nvarchar instead of varchar|||Markc. Thank you very much for your response. That did exactly what I
needed it to do.
One problem I'm seeing now is in my next step where I apply an xpath to
the @.ItemXML just to do the pagination. It seems that xpath step then
accounts for 85% of my query cost. I'm trying another approach just to
be sure I've covered all my bases and can get the most performant
solution, and I'm running into trouble because I'm still learning the
xml datatype & some other SQL 2005 features. Anyway, I though I'd try a
query against the xml variable, while also joining to my lookup tables
and creating a rownumber for pagination. I've tried several different
syntax with this, but it keeps breaking with "Invalid object name 'R'",
so if anyone can see what I'm doing wrong, again, I'd greatly
appreciate it.
SELECT ROW_NUMBER() OVER (ORDER BY rank DESC) AS RowNumber,
i.value('rank','int'),
i.value('itemid','int'),
i.value('catid','int'),
dbo.c.vch_categorytype AS cat,
i.value('pid','int'),
dbo.p.vch_providername_public AS provider,
i.value('ctid','int'),
i.value('link','varchar(300)'),
i.value('thumburl','varchar(300)'),
i.value('title','varchar(500)'),
i.value('published','datetime')
FROM R cross apply @.ItemXML.nodes('itemdata') R(i)
JOIN Categories_LU c ON R.i.value('catid','int') = c.i_category_id
JOIN Providers_LU p ON R.i.value('pid','int') = p.i_provider_id
WHERE RowNumber BETWEEN 1 AND 20 -- assume page 1, items 1-20
AND R.i.value('ctid','int') = 1 -- this would eventually be a dynamic
where clause|||I think
FROM R cross apply @.ItemXML.nodes('itemdata') R(i)
should be
FROM @.ItemXML.nodes('itemdata') R(i)
However, I believe there are other problems here as well. Suggest you
post the DDL for the two tables and some sample data.
Regards
Mark|||Sorry... I know @.ItemXML.nodes('itemdata') won't return the nodes... I
was thrashing around trying different things. Here are some other ways
I've tried to get this...
FROM R cross apply @.ItemXML.nodes('itemdata/item') R(i)
FROM FooTblNm cross apply @.ItemXML.nodes('itemdata/item') R(i)
... also have mixed up R.i.value and i.value... Nothing made a
difference. Always came back with the table ("R", "FooTblNm") being an
invalid object. Sure, I know answer #1 is I need to read a book so I
don't have to guess on syntax. :-)
Thanks again,
STA|||Here's a complete query with tmp table data...
Again, thanks for your help.
----
--
DECLARE @.ItemXML xml
SET @.ItemXML = '
<itemdata>
<item>
<rank>558</rank>
<itemid>11111111</itemid>
<catid>1</catid>
<pid>11</pid>
<ctid>1</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 1</title>
<published>2006-04-05T15:52:17</published>
</item>
<item>
<rank>558</rank>
<itemid>22222222</itemid>
<catid>22</catid>
<pid>50</pid>
<ctid>2</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 2</title>
<published>2006-04-05T15:52:17</published>
</item>
<item>
<rank>558</rank>
<itemid>333333333</itemid>
<catid>33</catid>
<pid>50</pid>
<ctid>3</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 3</title>
<published>2006-04-05T15:52:17</published>
</item>
</itemdata>'
-- CHECK TO SEE IF #tmps ARE THERE ALREADY...
-- THE PROBABLY DIDN'T GET DROPPED BECAUSE THE QUERY BROKE
IF object_id('tempdb..#tmpCats') IS NULL
BEGIN
CREATE TABLE #tmpCats (i_category_id int, vch_category
varchar(100))
INSERT #tmpCats VALUES (1,'Category 1')
INSERT #tmpCats VALUES (2,'Category 2')
INSERT #tmpCats VALUES (3,'Category 3')
END
IF object_id('tempdb..#tmpProviders') IS NULL
BEGIN
CREATE TABLE #tmpProviders (i_provider_id int, vch_provider
varchar(100))
INSERT #tmpProviders VALUES (1,'Provider 1')
INSERT #tmpProviders VALUES (2,'Provider 2')
INSERT #tmpProviders VALUES (3,'Provider 3')
END
SELECT ROW_NUMBER() OVER (ORDER BY rank DESC) AS RowNumber,
i.value('rank','int'),
i.value('itemid','int'),
i.value('catid','int'),
c.vch_category AS cat,
i.value('pid','int'),
p.vch_provider AS provider,
i.value('ctid','int'),
i.value('link','varchar(300)'),
i.value('thumburl','varchar(300)'),
i.value('title','varchar(500)'),
i.value('published','datetime')
FROM R cross apply @.ItemXML.nodes('itemdata/item') R(i)
JOIN #tmpCats c ON R.i.value('catid','int') = c.i_category_id
JOIN #tmpProviders p ON R.i.value('pid','int') = p.i_provider_id
WHERE RowNumber BETWEEN 1 AND 20 -- assume page 1, items 1-20
AND R.i.value('ctid','int') = 1 -- eventually a dynamic where clause
DROP TABLE #tmpCats
DROP TABLE #tmpProviders|||This should help get you started
;
WITH
XMLNodes(RowNumber,rank,itemid,catid,cat
,pid,provider,ctid,link,thumburl,tit
le,published)
AS(
SELECT ROW_NUMBER() OVER (ORDER BY i.value('rank[1]','int') DESC) AS
RowNumber,
i.value('rank[1]','int'),
i.value('itemid[1]','int'),
i.value('catid[1]','int'),
c.vch_category,
i.value('pid[1]','int'),
p.vch_provider,
i.value('ctid[1]','int'),
i.value('link[1]','varchar(300)'),
i.value('thumburl[1]','varchar(300)'),
i.value('title[1]','varchar(500)'),
i.value('published[1]','datetime')
FROM @.ItemXML.nodes('/itemdata/item') R(i)
JOIN #tmpCats c ON R.i.value('catid[1]','int') = c.i_category_id
JOIN #tmpProviders p ON R.i.value('pid[1]','int') = p.i_provider_id
WHERE R.i.value('ctid[1]','int') = 1
)
SELECT RowNumber,
rank,
itemid,
catid,
cat,
pid,
ctid,
link,
thumburl,
title,
published
FROM XMLNodes
WHERE RowNumber BETWEEN 1 AND 20|||mark,
You absolutely rock! Thank you so much for your help on this.
STA
Friday, March 9, 2012
Dynamic Deluxe!
I have a software system that needs to give the user to develop basically an
if statement. So IF PERSON_AGE > 15 AND MONTHLY_PURCHASE_AMT > 1000 THEN
GIVE THEM 1500 POINTS.
Here is the deal. I need to do it both during a back end process and
dynamically when a person comes to the counter. These marketing programs ar
e
definable by the instituion so we can't build the query ahead of time.
Anythoughts on a good way to do this. Keep in mind that there might be
multiple marketing programs active and the person qualifies for more then on
e
all should be able to display to the user. I would like to use a function so
that I can return a select statement with the messages to be presented to th
e
user.
Thanks for any thoughts.
Sammy DIt depends how complex your promotional formula needs to get but for
range-based criteria you could do something like this:
CREATE TABLE Promotions (promotion_code CHAR(10) PRIMARY KEY,
points_award INTEGER NOT NULL, age_min INTEGER NOT NULL, age_max
INTEGER NOT NULL, purchase_amt_min NUMERIC(10,2) NOT NULL,
purchase_amt_max NUMERIC(10,2) NOT NULL, ...)
Example. Promotions applicable to customer 1234:
SELECT P.promotion_code, P.points_award
FROM X, Promotions AS P
WHERE X.age BETWEEN P.age_min AND P.age_max
AND X.purchase_amt BETWEEN P.purchase_amt_min AND P.purchase_amt_max
AND customer = 1234
Where a particular attribute isn't relevant to the promotion just set
those values to be the min and max for the datatype.
David Portas
SQL Server MVP
--|||You might consider using a decision table program and not a database at
all.
if statement. So IF PERSON_AGE > 15 AND MONTHLY_PURCHASE_AMT > 1000 THEN
GIVE THEM 1500 POINTS.
Here is the deal. I need to do it both during a back end process and
dynamically when a person comes to the counter. These marketing programs ar
e
definable by the instituion so we can't build the query ahead of time.
Anythoughts on a good way to do this. Keep in mind that there might be
multiple marketing programs active and the person qualifies for more then on
e
all should be able to display to the user. I would like to use a function so
that I can return a select statement with the messages to be presented to th
e
user.
Thanks for any thoughts.
Sammy DIt depends how complex your promotional formula needs to get but for
range-based criteria you could do something like this:
CREATE TABLE Promotions (promotion_code CHAR(10) PRIMARY KEY,
points_award INTEGER NOT NULL, age_min INTEGER NOT NULL, age_max
INTEGER NOT NULL, purchase_amt_min NUMERIC(10,2) NOT NULL,
purchase_amt_max NUMERIC(10,2) NOT NULL, ...)
Example. Promotions applicable to customer 1234:
SELECT P.promotion_code, P.points_award
FROM X, Promotions AS P
WHERE X.age BETWEEN P.age_min AND P.age_max
AND X.purchase_amt BETWEEN P.purchase_amt_min AND P.purchase_amt_max
AND customer = 1234
Where a particular attribute isn't relevant to the promotion just set
those values to be the min and max for the datatype.
David Portas
SQL Server MVP
--|||You might consider using a decision table program and not a database at
all.
Sunday, February 26, 2012
Dynamic controls in report prompt
What I would like to do is have a drop down that would basically have a list of countries. When the user selects the USA, another drop down in the report prompt would have a filtered list of states in the USA. Finally, the user would select a state and a third drop down would have a list of cities. Obviously this would need to be done via post backs. Bottomline is can this be done. Can the report prompt be more dynamic. If not I will need to develope some aspx report prompt and pass the data, which I want to avoid. PLEASE HELP!That can be done with cascading parameters.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"AG" <AG@.discussions.microsoft.com> wrote in message
news:3F535CAF-8B50-4566-BE35-8BE4DC8762C4@.microsoft.com...
> What I would like to do is have a drop down that would basically have a
> list of countries. When the user selects the USA, another drop down in
> the report prompt would have a filtered list of states in the USA.
> Finally, the user would select a state and a third drop down would have a
> list of cities. Obviously this would need to be done via post backs.
> Bottomline is can this be done. Can the report prompt be more dynamic. If
> not I will need to develope some aspx report prompt and pass the data,
> which I want to avoid. PLEASE HELP!|||"Lev Semenets [MSFT]" <levs@.microsoft.com> schrieb im Newsbeitrag
news:eyMx04nVEHA.2520@.TK2MSFTNGP12.phx.gbl...
> That can be done with cascading parameters.
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
Could you please clarify on what database the description is based? Neither
in Northwind nor in Advantureworks is a table named Divisions.
r.|||Yes, this uses an imaginary database example. :) The documentation folks
have fixed this for next release.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roland" <nomail@.spamprotect.com> wrote in message
news:uHB10GtVEHA.1952@.TK2MSFTNGP12.phx.gbl...
> "Lev Semenets [MSFT]" <levs@.microsoft.com> schrieb im Newsbeitrag
> news:eyMx04nVEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > That can be done with cascading parameters.
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Could you please clarify on what database the description is based?
Neither
> in Northwind nor in Advantureworks is a table named Divisions.
> r.
>|||Actually the easy way is the postback method.
But you can develop your own javascript code to do this job, but I think its
harder to code and to debug.
Why the postback cause a problem for you?
"AG" <AG@.discussions.microsoft.com> a écrit dans le message de
news:3F535CAF-8B50-4566-BE35-8BE4DC8762C4@.microsoft.com...
> What I would like to do is have a drop down that would basically have a
list of countries. When the user selects the USA, another drop down in the
report prompt would have a filtered list of states in the USA. Finally, the
user would select a state and a third drop down would have a list of cities.
Obviously this would need to be done via post backs. Bottomline is can this
be done. Can the report prompt be more dynamic. If not I will need to
develope some aspx report prompt and pass the data, which I want to avoid.
PLEASE HELP!
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"AG" <AG@.discussions.microsoft.com> wrote in message
news:3F535CAF-8B50-4566-BE35-8BE4DC8762C4@.microsoft.com...
> What I would like to do is have a drop down that would basically have a
> list of countries. When the user selects the USA, another drop down in
> the report prompt would have a filtered list of states in the USA.
> Finally, the user would select a state and a third drop down would have a
> list of cities. Obviously this would need to be done via post backs.
> Bottomline is can this be done. Can the report prompt be more dynamic. If
> not I will need to develope some aspx report prompt and pass the data,
> which I want to avoid. PLEASE HELP!|||"Lev Semenets [MSFT]" <levs@.microsoft.com> schrieb im Newsbeitrag
news:eyMx04nVEHA.2520@.TK2MSFTNGP12.phx.gbl...
> That can be done with cascading parameters.
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
Could you please clarify on what database the description is based? Neither
in Northwind nor in Advantureworks is a table named Divisions.
r.|||Yes, this uses an imaginary database example. :) The documentation folks
have fixed this for next release.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roland" <nomail@.spamprotect.com> wrote in message
news:uHB10GtVEHA.1952@.TK2MSFTNGP12.phx.gbl...
> "Lev Semenets [MSFT]" <levs@.microsoft.com> schrieb im Newsbeitrag
> news:eyMx04nVEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > That can be done with cascading parameters.
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Could you please clarify on what database the description is based?
Neither
> in Northwind nor in Advantureworks is a table named Divisions.
> r.
>|||Actually the easy way is the postback method.
But you can develop your own javascript code to do this job, but I think its
harder to code and to debug.
Why the postback cause a problem for you?
"AG" <AG@.discussions.microsoft.com> a écrit dans le message de
news:3F535CAF-8B50-4566-BE35-8BE4DC8762C4@.microsoft.com...
> What I would like to do is have a drop down that would basically have a
list of countries. When the user selects the USA, another drop down in the
report prompt would have a filtered list of states in the USA. Finally, the
user would select a state and a third drop down would have a list of cities.
Obviously this would need to be done via post backs. Bottomline is can this
be done. Can the report prompt be more dynamic. If not I will need to
develope some aspx report prompt and pass the data, which I want to avoid.
PLEASE HELP!
Sunday, February 19, 2012
Dynamic columns for Flat File destination?
I have a database app, and we're implementing various data export features using SSIS.
Basically, it's a couple of straight extracts of various recordsets/views, etc. to CSV (flat files) from our SQL Server 2005 database, so I'm creating an SSIS package for each of these datasets.
So far, so good, but my problem comes here: My requirements call for users to select from a list of available columns the fields that they want to include in their exported file. Then, the package should run, but only output the columns specified by the user.
Does anyone have any idea as to the best way to accomplish this? To recap, at design time, I know which columns the users will have to choose from, but at run time, they will specify the columns to export to the flat file.
Any help or guidance here is greatly appreciated
You will need to create the package programatically in order to acheive this.
http://msdn2.microsoft.com/en-us/library/ms345167.aspx is a good place to start.
Donald
|||Thanks Donald... I have one more thing to ask. Is it possible to create and execute a package from scratch within a Script Task?
I really want to avoid scripting this stuff outside of the SSIS package (ie, in a VB.NET assembly), because my solution architecture depends on encapsulating all of the logic for SSIS stuff within the package.
If it's not possible from within a script task, could there possibly be another way to achieve this while preserving encapsulation of the logic within the SSIS package?
|||Yes it is possible to do it in a script task. the code is exactly the same as if you were doing it outside of SSIS (as long as you were using VB.Net of course :)
The best example I have seen so far is here: http://www.gotdotnet.com/codegallery/codegallery.aspx?id=042f5bda-78c6-4c94-a68e-c1917b036db3 Click on "Create Packages Dynamically" in the bottom right hand corner.
-Jamie
|||
Hi Jamie,
again, thanks for the reply... While that code is indeed a fine example of creating packages programmatically, there's a bit of a problem with trying to replicate it inside of SSIS.
That code sample (as well as pretty much all others I've found for building packages programatically) imports the Microsoft.SqlServer.Dts.Runtime.Wrapper and the rosoft.SqlServer.Dts.Pipeline.Wrapper namespaces. Apparently neither of these namespaces are available from within a SSIS script task. The VSA IDE won't let me add references to the assemblies containing these namespaces.
Is there something else I'm missing?
|||It is not possible to create packages or new objects within packages using SSIS.
You may be able to build the functionality into an assembly which could be called from a script - but you'll need to deploy the assembly separately.
Donald
|||Oops, I didn't know that! Sorry J Nail!
If I were you I would build it innto a custom task. Its not that difficult - not that much harder then doing it in the script task.
-Jamie
Subscribe to:
Posts (Atom)