Associating Data from Other Tables

Morning, all.

I have an input record that contains the following fields:

header_id_1
header_id_2
header_id_3
header_detail_1
header_detail_2

And I have a table, that contains the following fields

header_id_1
header_id_2
header_id_3
header_detail_1
header_detail_2

Okay. The kicker here is that in the table, the header_id's are already defined, but the details are blank. I need to insert the details where the header_id's of the input equal the header_id's of the table.

Is there a loop or something? I can *almost* see it in my head if I was just writing a SQL Query, but it involves a cursor, and I'm hoping SSIS will save me some of the aggravation...

Any ideas?
Isn't this just an update?

UPDATE TABLE
SET header_detail_1 = input_header_detail1,
header_detail_2 = input_header_detail2
WHERE header_id_1 = input_header_id_1
AND header_id_2 = input_header_id_2
AND header_id_3 = input_header_id_3

You could achieve this with an OLE DB Command transformation.|||Oh, hey! Yeah! Thanks!

Jim Work
|||You could also load the flat file (or whatever source you may have) to a staging table and then issue a batch update -- which would be faster than using the OLE DB Command to perform your updates...

To perform the batch update inside SSIS, you'd use an Execute SQL task in the control flow after you've loaded the staging table with a data flow.

Inside that Execute SQL task, your query would be something like:

UPDATE destination_table a, staging_table b
SET a.column1 = b.column1, a.column2 = b.column2
WHERE a.key1 = b.key1
AND a.key2 = b.key2
AND a.key3 = b.key3

It's real similar to the above query, except that you're using native SQL to perform the update rather than SSIS.

Aspnet_Profile data extraction

Hello,

in the new aspnet 2, there is a table called Aspnet_Profile wich contains user informations.

Look at the way they are filled

c9d365eb-6981-4c12-bfb8-e9b17445b26d Name:S:0:11:phone:S:11:9: William DOE567876567 <Binary data> 05/10/2005 12:27 35207ce2-8368-4914-95d7-2e0f51414d9c Name:S:0:8:phone:S:8:9: John DOE567876567 <Binary data> 05/10/2005 12:27

The first column is the UniqueIdentifier of user
The second is PropertyNames which contains the structure of the informations. For example here for the first line :
Name is a string wich begins at 0 and is 11 long
Phone is a string wich begins at 11 and is 9 long

The third column contains the informations

then you have here :
Row 1 :
Name : William DOE
Phone: 567876567

Row 2 : John DOE
Phone: 567876567

my problem is to be able to access those informations with a stored procedure.

Can Anyone help me ? thanks a lotYou can write a write a TSQL table-valued UDF that can split the formatted property names and get the lengths. You can then use the offset/lengths with SUBSTRING to get the actual values. The final casting can be done based on the type. But why are you trying to access this information in a SP? What happens if the next service pack or hotfix for .NET changes some of these formats? Maybe if you post what you are trying to do in the .NET newsgroup they will be able to suggest other ways to get the information that you need.

asp:sqldatasource - sum and contains

I have an asp:sqldatasource which is bound to a gridview

In addition to this I would like it to
a) see if there is a specif row/ item in it (ie item_id = 10 for any of the rows it has received) as I conditionally want to show another item outside of the gridview subject to if it is in the gridview or not

b) show the sum of all the values within a certain column of returned rows

Many thanks

not quite sure I understand your question a) but I for question b)

select sum(quantity) from table where id = 1

will return the sum of the quantity which id column equal to 1

ASP.NET SQL CONTAINS statement

I have an SQL db that I need to be able to search and display. I haveabout seven different columns and would like results to be returned toa datagrid based upon the search criteria entered by the user.
How do I construct a SELECT statement such as below using the ASP.NET 1.1+?
SELECT * FROM db.table WHERE CONTAINS ("searchable term")
I can get a column searched and return the results already, but if Imodify to search all columns and use the CONTAINS it will fail. Isthere a way to do this easily?
Thanks,
TRKneller

Your query is failing because you are using CONTAINS Microsoft Proprietry FULL TEXT search predicate when you need to use LIKE which is ANSI SQL used to search table column based data. Full Text is used for Text and NText data but it is an add on to SQL Server dependent on Microsoft Search Service and the Catalog must be populated to get search results. SQL Server creates an Arithmetic Pointer to Text and NText data in your file system. Run a search for LIKE and FULL Text in the BOL(books online). Hope this helps.

ASP.NET ReportViewer / Non-String parameters

I am displaying a local report in a ReportViewer control on an asp.net web page. My report contains non-string parameters. How can i set a non-string parameter value on a local report?You will need to call .ToString() on your value to set it on ReportParameter.|||That would result in a string being passed to the report. I want to pass a float or a datetime to the report.

The only way i've been able to get this to work is to create a typed dataset with the "parameters" i want sent and then use aggregation functions (first,sum, etc...) to get to the data into the report. That is a big workaround in order to get a few simple floats and times into my report. Has anyone been able to get this done using another method?
|||If the data type of the parameter is set to integer/float, you can do these operations. And you can always convert between types in a report expression.

ASP.NET Membership tables dissappear when db is attached to a different sql server instanc

A SQL Server 2005 (developer edition) database contains both application
data tables and ASP.NET membership tables and stored procedures and runs
without error on computer A. When the database is detached, copied to
computer B, and re-attached, the ASP.NET membership tables and stored
procedures are gone. If you know the cause/cure for this issue, I would
appreciate your help.
Thanks,
Keith
Are they gone, or just not visible? It could be a permissions issue
where you're connecting with elevated permissions on A and not on B.
--Mary
On Sat, 24 Feb 2007 22:22:06 -0800, "keith" <kbrickey@.dslextreme.com>
wrote:

>A SQL Server 2005 (developer edition) database contains both application
>data tables and ASP.NET membership tables and stored procedures and runs
>without error on computer A. When the database is detached, copied to
>computer B, and re-attached, the ASP.NET membership tables and stored
>procedures are gone. If you know the cause/cure for this issue, I would
>appreciate your help.
>Thanks,
>Keith
>

ASP.NET Membership tables dissappear when db is attached to a different sql server instanc

A SQL Server 2005 (developer edition) database contains both application
data tables and ASP.NET membership tables and stored procedures and runs
without error on computer A. When the database is detached, copied to
computer B, and re-attached, the ASP.NET membership tables and stored
procedures are gone. If you know the cause/cure for this issue, I would
appreciate your help.
Thanks,
KeithAre they gone, or just not visible? It could be a permissions issue
where you're connecting with elevated permissions on A and not on B.
--Mary
On Sat, 24 Feb 2007 22:22:06 -0800, "keith" <kbrickey@.dslextreme.com>
wrote:
>A SQL Server 2005 (developer edition) database contains both application
>data tables and ASP.NET membership tables and stored procedures and runs
>without error on computer A. When the database is detached, copied to
>computer B, and re-attached, the ASP.NET membership tables and stored
>procedures are gone. If you know the cause/cure for this issue, I would
>appreciate your help.
>Thanks,
>Keith
>