I would like to create a View (we'll call it FamilyView) using two tables, that I can then query against.
For example:
Parent
{
ID_PK,
Name,
PhoneNum,
Address
}
Child
{
ID_PK,
ParentID_FK,
Name
}
The view would return a dataset like this:
Parent.Name, Parent.PhoneNum, Parent.Address, Child.Name1, Child.Name2, Child.Name3... Child.NameN
William Smith, (555)555-5555, 123 Main Street, Susie, Peter, Bill Jr, Fred
Jason Jones, (666)666-6666, 54332 South Ave, Brian, Steven
Kay McPeak, (777)777-7777, 9876 Division NW, Kathy, Sally, Karen, Deb, Becky, Kendra, Ann, Edward
with an unknown number of children for each parent.
Then I would like to be able to query against this view with something like this:
SELECT * FROM FamilyView Where Child2 = 'Peter'
I have no idea how to write the SQL for this View. Is it possible?
Is this possible without using a cursor?
Thanks for any advice you all can give me.
BrianWhat Version of SQL Server?
If it's 2005, you can use CTE (Common Table Expreassions)
If it's 2000, you probably need to use a udf that returns a table|||It's SQL Server 2000.
Could you give me an example of how a UDF would be used to solve this please?|||So you want to find where the family tree for a child somewhere in the middle?|||I want to display each "family" in a single row in a result set and then be able to filter those families where the second child listed is 'Peter' (for example) and view only the families where Peter is the name of the second child. Does that make sense?
My real problem is a little more complex, but I thought that if I used this example it would eliminate a lot of explanation of the problem domain.|||Here you go, either a sproc, or a udf for set based stuff
CREATE TABLE Parent (
ID_PK int IDENTITY(1,1)
, [Name] varchar(20)
, PhoneNum varchar(20)
, Address varchar(30))
CREATE TABLE Child (
ID_PK int
, ParentID_FK int)
GO
INSERT INTO Parent([Name],PhoneNum, Address)
SELECT 'Annie', '111-111-1111', '1st Street' UNION ALL
SELECT 'Bob', '222-222-2222', '2nd Street' UNION ALL
SELECT 'Cathy', '333-333-3333', '3rd Street' UNION ALL
SELECT 'Don', '444-444-4444', '4th Street' UNION ALL
SELECT 'Emily', '555-555-5555', '5th Street' UNION ALL
SELECT 'Frank', '666-666-6666', '6th Street' UNION ALL
SELECT 'Georgette', '777-777-7777', '7th Street' UNION ALL
SELECT 'Harry', '888-888-8888', '8th Street'
INSERT INTO Child(ID_PK, ParentID_FK)
SELECT 1, null UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 2 UNION ALL
SELECT 4, 3 UNION ALL
SELECT 5, null UNION ALL
SELECT 6, 5 UNION ALL
SELECT 7, 6 UNION ALL
SELECT 8, 7
GO
SELECT * FROM Parent p LEFT JOIN Child c ON p.ID_PK = c.ID_PK
GO
CREATE FUNCTION udf_FindTree (@.Child varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @.p int, @.p_save int, @.rs varchar(8000)
SELECT @.p = 0, @.p_save = 0
SELECT @.p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = @.Child
--Loop Until @.@.rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @.p)
BEGIN
SELECT @.p_save = @.p
SELECT @.p = ParentID_FK FROM Child c WHERE ID_PK = @.p_save
-- The Last assignement is the top Parent
END
--Now Walk from the top Down until @.@.rowcount = 0
SELECT @.p = @.p_save
SELECT @.rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @.p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @.p)
BEGIN
SELECT @.rs = @.rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @.p
SELECT @.p = ID_PK FROM Child WHERE ParentID_FK = @.p
END
RETURN @.rs
END
GO
SELECT dbo.udf_FindTree('Cathy')
GO
SELECT * FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = 'Cathy'
GO
CREATE PROC usp_FindTree @.Child varchar(20)
AS
SET NOCOUNT ON
DECLARE @.p int, @.p_save int, @.rs varchar(8000)
SELECT @.p = 0, @.p_save = 0
SELECT @.p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = @.Child
--Loop Until @.@.rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @.p)
BEGIN
SELECT @.p_save = @.p
SELECT @.p = ParentID_FK FROM Child c WHERE ID_PK = @.p_save
-- The Last assignement is the top Parent
END
--Now Walk from the top Down until @.@.rowcount = 0
SELECT @.p = @.p_save
SELECT @.rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @.p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @.p)
BEGIN
SELECT @.rs = @.rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @.p
SELECT @.p = ID_PK FROM Child WHERE ParentID_FK = @.p
END
SELECT @.rs AS rs
SET NOCOUNT OFF
GO
EXEC usp_FindTree 'Cathy'
GO
DROP PROC usp_FindTree
DROP Function udf_FindTree
DROP TABLE Parent, Child
GO|||You could even do
SELECT DISTINCT dbo.udf_FindTree([name]) FROM Parent
GO|||Your work here has actually taught me quite a bit about UDFs and I appreciate that very much, Thank you!
But what I'm looking for is something closer to what this SQL generates.
USE Northwind
GO
SELECT OrderID,
coalesce(MAX(CASE OD.rowno WHEN 1 THEN P.ProductName END), '') AS Product1,
coalesce(MAX(CASE OD.rowno WHEN 2 THEN P.ProductName END), '') AS Product2,
coalesce(MAX(CASE OD.rowno WHEN 3 THEN P.ProductName END), '') AS Product3,
coalesce(MAX(CASE OD.rowno WHEN 4 THEN P.ProductName END), '') AS Product4,
coalesce(MAX(CASE OD.rowno WHEN 5 THEN P.ProductName END), '') AS Product5,
coalesce(MAX(CASE OD.rowno WHEN 6 THEN P.ProductName END), '') AS Product6,
coalesce(MAX(CASE OD.rowno WHEN 7 THEN P.ProductName END), '') AS Product7
FROM (SELECT a.OrderID, a.ProductID,
rowno = (SELECT COUNT(*)
FROM [Order Details] b
WHERE b.OrderID = a.OrderID
AND b.ProductID <= a.ProductID)
FROM [Order Details] a) AS OD
JOIN Products P ON P.ProductID = OD.ProductID
GROUP BY OD.OrderID
ORDER BY OD.OrderID
Use Northwind database and assume [Order Details] as parent and [Products] as the child. See how all the data between the two tables are displayed in one row (but separate NAMED columns: Product1, Product2, ... etc.)? That's what I'm looking for. If I could write this code into a View (Which I can't) then I could query against the returned dataset like this.
SELECT * FROM OrderProductView WHERE Product1 = 'Chang'
and I would get all the same columns, but only including the rows with OrderIDs: {10255, 10258, 10264, etc}.
The problem with the above code is that I HAVE to know the number of "child" (Product) elements expected per order at design time. Also, the CASE construct is not valid in a View.|||So you don't care about a tree, just a key and all it's attributive rows?
Maybe something like
http://weblogs.sqlteam.com/brettk/archive/2005/02/23/4171.aspx|||Yes! This appears to be exactly what I've been looking for. Thank you, thank you, thank you.
I was beginning to think this could only be executed in code outside the SQL.
I need to play with this a little to fully understand it all, but I think this will give me the results I need.
Thank you Brett for your patience and all your help!|||Just cut and paste the code example to see how it works
Good Luck
...oh, and you can buy me a margarita and we'll call it even|||Next time I'm in the Jersey area I might do just that. I really appreciate it.
And if you're ever in Grand Rapids...|||One more question...
My query is now too big to store in a local variable... I've managed to write the generated query to a file. Is there anyway I can execute this query from a text file?
Showing posts with label call. Show all posts
Showing posts with label call. Show all posts
Monday, March 26, 2012
Parent Package Variable issue?
I have noticed an issue with parent package variables. I have a package with multiple parent package variables defined, call them X, Y, and Z. I also have a parent package that calls this other package. The parent package has variable definitions for X and Z. It seems that the value for X will be passed along, and Y will give a warning since there is no variable of that name in the parent. The issue is that Z will not be passed along. It seems like the parent package configuration process stops after it encounters one missing variable.
Is this a know issue? Is it by design?Yep, I see the same thing.|||Good to know I'm not crazy! Thanks.|||Look for a fix in SP3, perhaps.
There is already a bug filed for this issue.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=174510
Parent Package Doesn't Recognize Child Settings
I have two issues:
I have a simple parent that uses an Execute Package Task to call a simple child package. The child package has a Data Flow that I disabled. When running the child package by itself, the data flow task is bypassed. When running the package via the parent the data flow task executes.
Second issue is when you disable the package configurations in the child, the parent doesn't recognize that it's diables and tries to load the configurations.
It's almost like there are some settings in a child that get ignored by the parent?
Anyone else experience this?
thx
My fault, pls ignore...Parent package call to Child package
I run into some issues and really need some expert help here.
Here is the problem. I have two packages (parent.dtsx and child.dtsx). Both package have its own configuration file (parent.dtsConfig and child.dtsConfig). The file Child.dtsConfig contains a variable (i.e. "X") that is to be used by Child.dtsx.
Inside parent.dtsx. there is a package-task that calls into Child.dtsx. It worked perfectly well if I run parent.dtsx using Dtexec or from inside SSIS's IDE.
Now I want to programmably call "parent.dtsx" from my C# code. I loaded package using "app.LoadPackage"... Inside C# code, I want to reconfigure Child-package's variable ("X"). I then loaded in "Child.dtsx". However when I run "parent.dtsx" and child.dtsx still loads the original value for "X". The reconfigured value for "X" is not updated.
Please help on how to get around this issue.
Thanks.
Are you changing the configuration file or the value in the package itself? If you change the value in the package, it will be overwritten by the value from the config file at runtime.|||Your comments are exactly CORRECT...
I load the package and change the values (for both parent and chiled) inside the C# code. Yes, when I kick off parent package from C#, the child package load variables' value from child's configuration file.... This is NOT what I want.. Do you know any way to avoid this ? How do I persist the udpated values ?
|||Change the config file information with your C# code. Or use a SQL Server based configuration and update the table before each child package execution.|||
Phil Brammer wrote:
Change the config file information with your C# code. Or use a SQL Server based configuration and update the table before each child package execution.
Or don't use configurations. If you are running the packages from your code, and you are setting the values each time you load and execute the packages, what purpose are the configurations serving?
|||Thank you... Your comments are valid suggestions.
However,
first, I have a master package (parent) and many child packages. Each individual child package has its own configuration file (childXXX.dtsConfig) which is an XML file. We did not use SQL as configuration. We want to reuse this entire package suite for two different business domains. In one scenario, we run packages using DTEXEC (or scheduled job) and it worked well. In another scenario, we want to RE-USE the entire package suite from C# code. I want to re-configure each packages inside C# code. And lauch the master package and HOPEFULLY execute all child packages with updated variable values (rather than reading from configuration files because configuration file contains old-values which I do not want to load). Those configuration files
So i need to keep all XML configuration files.
Secondly even if I do not use configuration file, I still cannot persist child's updated variable values. I have set up a simple pair of package (parent and child) to verify it. Inside parent package, i have a task to execute the child package... And i reconfigure child package and start to run parent package inside C#. I found that Child package's configuration updates are lost.
I have zipped all of my testing files and wish to send you all files via email if you are interest. I would definitely appreciate your help as I am stuck now.
Please help.
|||Right, but the configuration files override any changes you make with C# as John stated. How do you propose to get around that?|||
Thank you... Your comments are valid suggestions.
However,
first, I have a master package (parent) and many child packages. Each individual child package has its own configuration file (childXXX.dtsConfig) which is an XML file. We did not use SQL as configuration. We want to reuse this entire package suite for two different business domains. In one scenario, we run packages using DTEXEC (or scheduled job) and it worked well. In another scenario, we want to RE-USE the entire package suite from C# code. I want to re-configure each packages inside C# code. And lauch the master package and HOPEFULLY execute all child packages with updated variable values (rather than reading from configuration files because configuration file contains old-values which I do not want to load). Those configuration files
So i need to keep all XML configuration files.
Secondly even if I do not use configuration file, I still cannot persist child's updated variable values. I have set up a simple pair of package (parent and child) to verify it. Inside parent package, i have a task to execute the child package... And i reconfigure child package and start to run parent package inside C#. I found that Child package's configuration updates are lost.
I have zipped all of my testing files and wish to send you all files via email if you are interest. I would definitely appreciate your help as I am stuck now.
Please help.
In your simple test, are you re-saving the child package after changing the variable settings? When you execute the parent from code, it will load the child package from the location pointed to by the package connection manager. It will not run the one you have loaded in your program.
I don't think you can do what you are trying to do. As Phil mentioned, the configurations will always take precedence over values saved in the package. If you want to run the same packages with different configurations, try using two different sets of configuration files, or have the parent package pass all values to the child packages through a parent package configuration. That way, you set the parent values when you execute it, and all the child packages pick up their values from it.
|||Thanks Phil and John for prompt response...
In my simple test, I re-saved the re-configured parent and child packages to new XML files (using Application.SaveToXML(..) ) and reloaded them back with the hope of child package not loading configuration from configuration files. It did not work.... i.e. Child package still loaded variable values from child.dtsConfig file.
I have also tried to overwrite the configuration file using SSIS's API (i.e. Package.ExportConfigurationFile (....). This function seems to have some bugs that does NOTHING always.. Nothing is written out to file. Here is a posing that complains this API: http://sqljunkies.com/Forums/Search/default.aspx?SearchFor=1&SearchText=neetash
What I thought is: If i could overwrite the configuration file with updated variable setting (from C#), then the issue is solved. However the API "Package.ExportConfigurationFile(...)" is useless.
|||Even if you were able to use the ExportConfigurationFile function - wouldn't that impact the ability to run the packages directly through DTEXEC? You'd effectively be altering both ways of running the packages.
If that is acceptable, I'd still suggest just loading the config file into an XML DOM object, change what you need to change, and save it back out.
If you need to keep the two methods of running the packages seperate, you'd still need to go with Parent Package configurations.
Monday, March 12, 2012
Parameters via a web service
Hy,
I have to build a report. this report has to be call by a web service. My method to call this report is :
[WebMethod]
public void Amende()
{
ReportingService rs = new localhost.ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
byte[] ResultStream; // bytearray for result stream
string[] StreamIdentifiers; // string array for stream idenfiers
string OptionalParam = null; // string out param for optional parameters
ParameterValue[] optionalParams = null; // parametervalue array for optional parameters
Warning[] optionalWarnings = null; // warning array for optional warnings
ResultStream = rs.Render("/SwatFillingDocuments/AMAD AYDIN 1198 2005 MD", "PDF", null,
"<DeviceInfo><StreamRoot>/RSWebServiceXS/</StreamRoot></DeviceInfo>", null, null,
null, out OptionalParam, out OptionalParam, out optionalParams,
out optionalWarnings, out StreamIdentifiers);
// Write the report to Response
HttpContext.Current.Response.BinaryWrite(ResultStream);
}
But in my report I have a parameter. And I have to give a value at this parameter via my web service. Is it possible to do that with the method Render?
oki I found how to give a parameter. So my code looks like that now:
[WebMethod]
public void Amende( string dossierId )
{
ReportingService rs = new localhost.ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
byte[] resultStream; // bytearray for result stream
string[] streamIdentifiers; // string array for stream idenfiers
string optionalParam = null; // string out param for optional parameters
ParameterValue[] optionalParams = null; // parametervalue array for optional parameters
// Prparation de la valeur passe en paramètre
ParameterValue[] parameters = new ParameterValue[1];
parameters[0] = new ParameterValue();
parameters[0].Name = "DossiersId";
parameters[0].Value = dossierId;
DataSourceCredentials[] credentials = null;
string showHideToggle = null;
string historyID = null;
Warning[] optionalWarnings = null; // warning array for optional warnings
resultStream = rs.Render("/SwatFillingDocuments/AMAD AYDIN 1198 2005 MD", "PDF",
historyID, @."False", parameters,credentials,showHideToggle,out optionalParam,
out optionalParam,out optionalParams, out optionalWarnings,out streamIdentifiers);
HttpContext.Current.Response.BinaryWrite(resultStream);
}
But when I put a parameter in my web service I have an error :
System.Net.WebException: The request failed with HTTP status 400: Bad Request.at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at localhost.ReportingService.Render(String Report, String Format, String HistoryID, String DeviceInfo, ParameterValue[] Parameters, DataSourceCredentials[] Credentials, String ShowHideToggle, String& Encoding, String& MimeType, ParameterValue[]& ParametersUsed, Warning[]& Warnings, String[]& StreamIds) in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\listreports\5d760f1a\6c0731c0\App_WebReferences.n1-mytpw.0.cs:line 1706
at Service.Amende(String dossierId) in c:\projects\ListReports\App_Code\Service.cs:line 36
Can you help me please? If I use this mothed without parameter it works but when i try to give a parameter it fails.Thank you|||
I do this in VB. This writes the report out to a disk after you get the byte array. This code is from inside a class that I wrap the process in so I havent't looked at it in awhile but it has been working without errors.
Public Sub RenderWriter()
Dim parameters() As MyReportService.ParameterValue
parameters = GetParameters()
Dim encoding As String
Dim mimeType As String
Dim parametersUsed() As MyReportService.ParameterValue
Dim warnings() As MyReportService.Warning
Dim streamIds() As String
'render the report
Dim data() As Byte
'data = _rs.Render(Me._ReportItem.Path, _Format.Name, Nothing, Nothing, parameters, Nothing, Nothing, encoding, mimeType, parametersUsed, warnings, streamIds)
data = _rs.Render(ReportItem.Path, Format.Name, Nothing, Nothing, parameters, Nothing, Nothing, encoding, mimeType, parametersUsed, warnings, streamIds)
'//create a file stream to write the output
'Dim fileName As String = _OutputPath & "\" & _ReportItem.Name & _Format.Extension
Write(data)
End Sub
Private Sub Write(ByVal data() As Byte)
Dim fs As New System.IO.FileStream(Me.FileName, System.IO.FileMode.OpenOrCreate)
Dim writer As New System.IO.BinaryWriter(fs)
writer.Write(Data, 0, Data.Length)
writer.Close()
fs.Close()
End Sub
Private Function GetParameters() As MyReportService.ParameterValue()
Dim i As Integer
Dim len As Integer = _ParamValues.Count - 1
Dim returnValues(len) As MyReportService.ParameterValue
For i = 0 To len
returnValues(i) = New MyReportService.ParameterValue
returnValues(i).Name = _ParamValues.Item(i).Name
returnValues(i).Value = _ParamValues.Item(i).Value
Next i
Return returnValues
End Function
Subscribe to:
Posts (Atom)