One for the SQL Gurus: Split a delimited field into rows

Hi.

I'm trying to write an SQL Query that will take a delimited field and return each item as a row.

Example

Take the AuthorizedRoles and TabID fields from the Tabs table

AuthorizedRoles TabID
0;11;__________1
0; 15 ;17;______6
-2;____________7

I would like to return a unique record for each Authorized Role

AuthorizedRole TabID
0____________1
11___________1
0____________6
15___________6
17___________6
-2___________7

Any ideas?

Cheers
Davecheck if thisarticle helps

hth|||Thanks Dinakar. I was hoping to do this entirely within the SELECT statement but realise that's probably impossible without built in SQL commands like split etc..

This example looks like a good place to start. All other examples I've seen require temp tables and Lookup tables.

Cheers
Dave

One DELETE sql statement to delete from two tables

I am trying to write one sql statement that deletes from two tables. Is it possible ? If yes, any thoughts ?

I think Triggers may help for this scenario...

I am not sure about one delete statement to delete from two tables.

Thanks

Sreekanth

|||

If both tables has the relation (child-master with foreign key), then you can use the ON DELETE CASCADE on your Primary Key (Master) constraint.

Code Snippet

Create MasterTable

(

Id int Primary Key On Delete Cascade,

..

..

)

Create Childtable

(

Master_TableId int References MasterTable(Id)

..

..

..

)

If the tables doesn’t have any relation (logically bound), then you can use the Trigger to delete the values from the tables – using DELETED special table.

Code Snippet

Create trigger trigger_name On FirstTable For Delete

As

Begin

Delete From SecondTable Where ID in (Select ID from DELETED)

End;

Go

Delete From FirstTable

If you use SQL Server 2005, then you can use the OUTPUT clause, to get the deleted values from the current table, and supply those deleted values to remove the records from other table.

Code Snippet

Declare @.DeletedIds Table

(

Id int

)

Delete FirstTable OUTPUT DELETED.ID INTO @.DeletedIds Where <condition>;

Delete From SecondTable Where ID in (Select Id from @.DeletedIds);

|||

More information may be required for answering this. The simple answer is, no, you cannot "technically" do this. Any method that you can simulate this will technically be multiple SQL operations. Triggers, ON CASCADE constraints, etc.

There is really no need for one statement to delete from multiple tables, the key for your needs is probably that you want to make sure that if one statement completes, then the other statement completes. This is referred to as an atomic operation, and is managed by Transactions. So, simplistically (you need error handling/messaging for sure) in the following:

BEGIN TRY

BEGIN TRANSACTION

DELETE 1

DELETE 2

COMMIT TRANSACTION

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

END CATCH

If DELETE 2 fails, but DELETE 1 succeeds, DELETE 1 will be undone.

sql

one configuration file across multiple packages.

Hi,

At just the point at which I was going to write some verbose schpeel, I found this, which really does it all for me:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1163727&SiteID=1&mode=1

Basically it seems that whilst you can indeed share a config file, it doesn't aggregate sets of say different connection managers, into a superset - you choose to reuse, but it actually overwrites. Any ideas, anyone?

Just edit the Config file to add manually the second configuration. Notice that by doing so you may receive warnings when executing the package as some configurations in the file do not exist in the package using the file. My advice is to use separate configuration files.

|||

'...My advice is to use separate configuration files...'

In the circumstances Rafael, I think this is the best advice. I must say I find it disappointing that one can only share config files in this half-baked way. It would be much better to have one config file shared across 2 packages for say connection managers A,B,C,D,E, where package 1 used connection managers A,B,C, and package 2 used C,D,E.

Maybe that's something for Microsoft to think about...

Thanks for your input Rafael,

Tamim.

|||

BTW,

SQL Server table based configurations behaves more on the way you want. You can have a single table with a row per each configuration value...no warnings if only some are used within the package.

omitting null columns

Hey everyone, I haven't got any idea where to even start looking for a solution to this problem so any help would be appreciated. I'm trying to write a stored procedure that will return a table yet omit any columns that don't have anything written to them, so any columns that have no data will not be present in the table returned after the stored procedure.

Thanks in advance.

Hi Mitch,

Here you could take advantage of "IS NOT NULL" for example;

SELECT
*
FROM
MyTable
WHERE
ColumnName IS NOT NULL

|||Is there a way to do it more like

SELECT
*
FROM
MyTable
WHERE
AnyColumn IS NOT NULL

certain tables have different column names so I need the code to be flexible enough to omit any column it finds that is null in any table I give it.
|||Wait sorry that doesn't work, that tell it to select any rows that contain anything that is not null in 'x' column. I need it to select that row still but omit 'x' column so.

| A | B | C |
| 1 | | 2 |

becomes

| A | C |

| 1 | 2 ||||

just concatenate the columns, this assumes that the SET CONCAT_NULL_YIELDS_NULL is ON (which is the default)

Example
CREATE TABLE TestNulls (c1 INT,c2 INT, c3 INT,c4 INT,c5 INT, c6 INT)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (2,1,1,1,1,1)
INSERT TestNulls VALUES (3,1,1,1,1,1)
INSERT TestNulls VALUES (4,NULL,1,1,1,1)
INSERT TestNulls VALUES (5,1,1,1,1,1)
INSERT TestNulls VALUES (6,1,1,1,1,1)
INSERT TestNulls VALUES (7,1,1,NULL,1,1)

SELECT *
FROM TestNulls
WHERE C1+C2+C3+C4+C5+C6 IS NOT NULL

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||

Never mind the answer I gave you before, I didn't see this requirement

You should handle stuff like this client side not server side it is just adding unnecessary processing on the DB side

To do this on the DB you will have to use <evil>dynamic SQL</evil> dump the result in a temp table and build the SELECT statement dynamically by using a bunch of EXISTS...not pretty at all

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Menace is right: The dynamic SQL to accomplish what you want is pretty ugly. Here is an example of one way to do it:

create table dbo.example
( column_01 integer,
column_02 float,
column_03 integer,
column_04 varchar(30)
)
go

insert into example values (1, null, 2, 'This is a test.')
insert into example values (2, null, null, null)
insert into example values (3, null, 3, 'Tell me.')
go

declare @.execString varchar (6000)
declare @.columnString varchar (5000) set @.columnString = ''

select @.columnString = @.columnString + x.columnName + ','
from ( select q.colid,
cast (q.columnName as varchar(20)) as columnName
from ( select max ( case when column_01 is null then 0
else 1 end ) as column_01,
max ( case when column_02 is null then 0
else 2 end ) as column_02,
max ( case when column_03 is null then 0
else 3 end ) as column_03,
max ( case when column_04 is null then 0
else 4 end ) as column_04
from example
) p
unpivot
( colid for columnName in
( [column_01], [column_02], [column_03], [column_04] )
) q
group by q.colid, q.columnName
having q.colid <> 0
) x

select @.execString
= 'select '
+ left (@.columnString, len(@.columnString)-1)
+ ' from example '

exec ( @.execString )

-- -- Output: --

-- column_01 column_03 column_04
-- -- --
-- 1 2 This is a test.
-- 2 NULL NULL
-- 3 3 Tell me.

|||

Menace is right: The dynamic SQL to accomplish what you want is pretty ugly. Here is an example of one way to do it:

create table dbo.example
( column_01 integer,
column_02 float,
column_03 integer,
column_04 varchar(30)
)
go

insert into example values (1, null, 2, 'This is a test.')
insert into example values (2, null, null, null)
insert into example values (3, null, 3, 'Tell me.')
go

declare @.execString varchar (6000)
declare @.columnString varchar (5000) set @.columnString = ''

select @.columnString = @.columnString + x.columnName + ','
from ( select q.colid,
cast (q.columnName as varchar(20)) as columnName
from ( select max ( case when column_01 is null then 0
else 1 end ) as column_01,
max ( case when column_02 is null then 0
else 2 end ) as column_02,
max ( case when column_03 is null then 0
else 3 end ) as column_03,
max ( case when column_04 is null then 0
else 4 end ) as column_04
from example
) p
unpivot
( colid for columnName in
( [column_01], [column_02], [column_03], [column_04] )
) q
group by q.colid, q.columnName
having q.colid <> 0
) x

select @.execString
= 'select '
+ left (@.columnString, len(@.columnString)-1)
+ ' from example '

exec ( @.execString )

-- -- Output: --

-- column_01 column_03 column_04
-- -- --
-- 1 2 This is a test.
-- 2 NULL NULL
-- 3 3 Tell me.

|||

use below if you have the list of columns otherwise.. you'll have to query the syscolumn table.

select *
from mytable
where column1 is not null
and column2 is not null
and column3 ...

Mitch Wardrop wrote:

Is there a way to do it more like

SELECT
*
FROM
MyTable
WHERE
AnyColumn IS NOT NULL

certain tables have different column names so I need the code to be flexible enough to omit any column it finds that is null in any table I give it.

OLEDB source - Use table or select only columns needed.

Hi All,

With the OLEDB source, is it wrong to use a table / view as a source and only check the columns required or is it beneficial to write a select col1, col2 etc etc as a SQL command?

I cannot see any difference in performance between the two.

Thanks.

Always always always use a SQL command so as to avoid the situation documented here: http://blogs.conchango.com/jamiethomson/archive/2006/02/21/2930.aspx I can't stress this enough.

Also check #4 here: http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2554.aspx Basically, only pull in the data that you need otherwise performance will suffer.

-Jamie

|||Thanks Jamie but...

I would still like to know the underlying reason it is bad :)

Accepted that Select * is bad due to many reasons but:
My "tables" are views which themselves only select columns required for the data flow and nothing more.

Basically I am being lazy - I write the columns out in the view and don't _really_ want to write them again in SSIS :)

From a performance point of view, the above method is exactly the same either way. I cannot and have not seen what you described.

Will play around some more and try find a reason (unless someone wants to save us the trouble....)|||

Well if nothing else I would do it in the interests of best practice. And also cos I'm picky - I hate seeing a selected table rather than a SQL statement :)

-Jamie

|||

Jamie Thomson wrote:

Well if nothing else I would do it in the interests of best practice. And also cos I'm picky - I hate seeing a selected table rather than a SQL statement :)

-Jamie

A counter to that is I hate seeing any form of SQL in SSIS. Rather have the logic in a view / proc or just pull from the table. Make life easier when looking for bugs.
(yes, you could make a rule such as "do not use anything more that Select *" :)|||

Let's agree to disagree! :)

-J

OLE DB Destination not writing data

I am using OLE DB Destination to write data to a SQL server database. However, nothing is written to the database though there is no error reported. See the following output:

SSIS package "Tbl_Dim_Dates.dtsx" starting.

Information: 0x4004300A at Tbl_Dim_Dates, DTS.Pipeline: Validation phase is beginning.

Information: 0x4004300A at Tbl_Dim_Dates, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Tbl_Dim_Dates, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Tbl_Dim_Dates, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Tbl_Dim_Dates, DTS.Pipeline: Execute phase is beginning.

Information: 0x402090DF at Tbl_Dim_Dates, OLE DB Destination [2396]: The final commit for the data insertion has started.

Information: 0x402090E0 at Tbl_Dim_Dates, OLE DB Destination [2396]: The final commit for the data insertion has ended.

Information: 0x40043008 at Tbl_Dim_Dates, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Tbl_Dim_Dates, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Tbl_Dim_Dates, DTS.Pipeline: "component "Date extract to file" (924)" wrote 3652 rows.

Information: 0x4004300B at Tbl_Dim_Dates, DTS.Pipeline: "component "Raw File Destination" (2518)" wrote 3652 rows.

Information: 0x4004300B at Tbl_Dim_Dates, DTS.Pipeline: "component "OLE DB Destination" (2396)" wrote 3652 rows.

SSIS package "Tbl_Dim_Dates.dtsx" finished: Success.

The program '[2708] Tbl_Dim_Dates.dtsx: DTS' has exited with code 0 (0x0).

this might be a bug. try recreating the data flow.|||

You should use SQL Server Profiler to see what is being sent to the database and what happens within SQL Server. We are pretty much just a client of SQL in such cases, and that's a good to place to start looking for the problem.

Donald

|||

Thanks for all your advices.

ghe

OLE DB Destination = temp table

Is it possible to write a dtsx so that the destination of an "OLE DB
Destination" Data Flow Destination is a temp table that is created earlier
in the dtsx ?On Mar 13, 7:05 pm, "John Grandy" <johnagrandy-at-gmail-dot-com>
wrote:
> Is it possible to write a dtsx so that the destination of an "OLE DB
> Destination" Data Flow Destination is a temp table that is created earlier
> in the dtsx ?
You may want to repost on the DTS/SSIS message groups. They are:
microsoft.public.sqlserver.dts and
microsoft.public.sqlserver.integrationsvcs. Sorry I could not be of
greater assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer

OLE DB Destination - table name variable

Hello,

In my Data Flow I have a OLE DB Destination that needs to get the table name to write the data to dynamicaly from a variable I created.

So I select "table name or view name variable" from the Data access mode and select my variable below. So far so good, but when I click "ok" I get the following error message :

Error at Data Flow Task [OLE DB Destination [45]]: A destination table name has not been provided.
Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)

Am I doing something wrong or is there another way to set a variable table name for the OLE DB Destination ?

Using a variable in this way works fine for me on RTM.

I can get that error if I do not supply a valid table table in my variable before trying to use it in the OLE-DB Destination, which in itself makes sense too.|||Thanks ... I forgot to give the variable a valid default value, now it works.

OldValuesParameterFormatString SqlDataSource Update

OK guys, I'm sure I'm doing something stupid here but after 2 days I find myself extremely frusted... Any help would be appreciated

I'm tring to write an update procedure for an SqlDataSource control that will allow me to chage the primary key values of a record in the table. Everything seems to work fine with the exception of obtaining the old values (the primary key before its changed by the end user) during the update. I've read several articles on the web an in the forums on this topic. Much of it has to do with the .NET beta version alteration from "original_{0}" to "{0}". I believe those conversations do not describe my problem.

Anyhow, onto some source code. Here's the ASP source code for a grid view and sql data source.

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="productName,productURL" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display."> <Columns> <asp:CommandField ShowSelectButton="True" /> <asp:BoundField DataField="productName" HeaderText="productName" ReadOnly="True" SortExpression="productName" /> <asp:BoundField DataField="productURL" HeaderText="productURL" ReadOnly="True" SortExpression="productURL" /> <asp:BoundField DataField="productSupportURL" HeaderText="productSupportURL" SortExpression="productSupportURL" /> <asp:BoundField DataField="description" HeaderText="description" SortExpression="description" /> <asp:BoundField DataField="modified" HeaderText="modified" SortExpression="modified" /> <asp:BoundField DataField="userid" HeaderText="userid" SortExpression="userid" /> <asp:BoundField DataField="useridDomain" HeaderText="useridDomain" SortExpression="useridDomain" /> <asp:BoundField DataField="sortOrder" HeaderText="sortOrder" SortExpression="sortOrder" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SBAAdminConnectionString1%>" DeleteCommand="DELETE FROM [homePageList] WHERE [productName] = @.old_productName AND [productURL] = @.old_productURL" InsertCommand="INSERT INTO [homePageList] ([productName], [productURL], [productSupportURL], [description], [modified], [userid], [useridDomain], [sortOrder]) VALUES (@.productName, @.productURL, @.productSupportURL, @.description, @.modified, @.userid, @.useridDomain, @.sortOrder)" ProviderName="<%$ ConnectionStrings:SBAAdminConnectionString1.ProviderName%>" SelectCommand="SELECT [productName], [productURL], [productSupportURL], [description], [modified], [userid], [useridDomain], [sortOrder] FROM [homePageList]" UpdateCommand="UPDATE homePageList SET productSupportURL = @.productSupportURL, description = @.description, modified = @.modified, userid = @.userid, useridDomain = @.useridDomain, sortOrder = @.sortOrder, productName = @.productName, productURL = @.productURL WHERE (productName = @.original_productName) AND (productURL = @.original_productURL)" OldValuesParameterFormatString="original_{0}"> <DeleteParameters> <asp:Parameter Name="old_productName" Type="String" /> <asp:Parameter Name="old_productURL" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="description" Type="String" /> <asp:Parameter Name="modified" Type="DateTime" /> <asp:Parameter Name="userid" Type="String" /> <asp:Parameter Name="useridDomain" Type="String" /> <asp:Parameter Name="sortOrder" Type="Single" /> <asp:Parameter Name="productName" Type="String" /> <asp:Parameter Name="productURL" Type="String" /> <asp:Parameter Name="productSupportURL" Type="String" /> <asp:ControlParameter ControlID="GridView1" Direction="InputOutput" Name="original_productName" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="GridView1" Direction="InputOutput" Name="original_productURL" PropertyName="SelectedValue" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="productName" Type="String" /> <asp:Parameter Name="productURL" Type="String" /> <asp:Parameter Name="productSupportURL" Type="String" /> <asp:Parameter Name="description" Type="String" /> <asp:Parameter Name="modified" Type="DateTime" /> <asp:Parameter Name="userid" Type="String" /> <asp:Parameter Name="useridDomain" Type="String" /> <asp:Parameter Name="sortOrder" Type="Single" /> </InsertParameters> </asp:SqlDataSource>

I added a text box for every field in the grid to the form and update the fields when the user selects a record in the GridView. I then added an Update button to the form and tied the event to this procedure: 

Protected Sub bUpdate_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles bUpdate.Click'if the user has not entered key fields!If missingKeyFields()Then Exit Sub End If Dim count, soAs Integer Dim uDomain, uidAs String'parse out the user id and domain.Try If HttpContext.Current.User.Identity.Name =""Then uid ="unknow" uDomain ="unknown"Else uid = HttpContext.Current.User.Identity.Name uid = uid.Substring(uid.IndexOf("\") + 1) uDomain = uid.Substring(0, uid.IndexOf("\") - 1)End If Catch exAs Exception uid ="unknow" uDomain ="unknown"End Try'fill in the parameters '@.productName, @.productURL, @.productSupportURL, @.description, @.modified, @.userid, @.useridDomain, @.sortOrderMe.SqlDataSource1.UpdateParameters.Item("productName").DefaultValue =Me.tbProductName.TextMe.SqlDataSource1.UpdateParameters.Item("productURL").DefaultValue =Me.tbProductURL.TextMe.SqlDataSource1.UpdateParameters.Item("productSupportURL").DefaultValue =Me.tbProductSupportURL.TextMe.SqlDataSource1.UpdateParameters.Item("description").DefaultValue =Me.tbProductDescription.TextMe.SqlDataSource1.UpdateParameters.Item("modified").DefaultValue = NowMe.SqlDataSource1.UpdateParameters.Item("userid").DefaultValue = uidMe.SqlDataSource1.UpdateParameters.Item("useridDomain").DefaultValue = uDomainMe.SqlDataSource1.UpdateParameters.Item("sortOrder").ConvertEmptyStringToNull =True If Integer.TryParse(Me.tbSortOrder.Text, so)Then Me.SqlDataSource1.UpdateParameters.Item("sortOrder").DefaultValue = soElse Me.SqlDataSource1.UpdateParameters.Item("sortOrder").DefaultValue =""End If'insert the record. count =Me.SqlDataSource1.Update()Me.lCommandInfo.Text = count.ToString +" record was updated."End Sub
 
The net result of the procedure is the message "0 record was updated." when I select a record in the gridView and then edit the "productName" field and click Update.

In an attempt to trace down the problem I added this code:

Protected Sub SqlDataSource1_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlDataSource1.Updating Message.Text = e.Command.CommandText Message.Text +="<br>"For lpAs Integer = 0To e.Command.Parameters.Count - 1 Message.Text += e.Command.Parameters(lp).ParameterName +"=" + e.Command.Parameters(lp).Value Message.Text +="<br>"Next End Sub

The message label displays the following text:

UPDATE homePageList SET productSupportURL = @.productSupportURL, description = @.description, modified = @.modified, userid = @.userid, useridDomain = @.useridDomain, sortOrder = @.sortOrder, productName = @.productName, productURL = @.productURL WHERE (productName = @.original_productName) AND (productURL = @.original_productURL)@.description=interesting!@.modified=3/15/2007 4:07:31 PM@.userid=unknow@.useridDomain=unknown@.sortOrder=@.productName=ok22@.productURL=http@.productSupportURL=http@.original_productName=ok@.original_productURL=ok

Notice how the "@.original_productURL" has the same value as the "@.original_productName"? This should not be the case. The @.original_productURL should equal "http" since I didn't update the field's value. It seems like the second field of the primary key is not getting updated or passed to the @.original_productURL parameter. Since .NET handles the @.original_{0} parameters I don't know why the field isn't being properly updated.

Any thoughts on how to track down the root cause of my problem... Or better yet any thoughts on a work around to my problem?

Thanks,
Johnny

way back in school i was taught update is the combination of select ,delete & insert....since u r changing the primarykey........select the row in temp variables,make the changes in the varioable,delete original from database & insert the modified row.....

this can be done by stored procedure or a datareader object easily.

|||

That does sound like a work around for my current situation, but I would hate to re-invent the wheel by creating temp variables that mimic the functionality of the OldValues parameter array.

Any other thoughts?

Johnnny

|||if u find another way let me know|||

OK here's the easiest work around I could find.

1) I removed the control binding for the parameters "original_productName" and "original_productURL". This was set to "GridView1.SelectedValue".
2) I manually set the parameters in my button click subroutine:
Me.SqlDataSource1.UpdateParameters.Item("original_productName").DefaultValue =Me.GridView1.SelectedDataKey.Item(0)
Me.SqlDataSource1.UpdateParameters.Item("original_productURL").DefaultValue =Me.GridView1.SelectedDataKey.Item(1)

I hope this helps others that run into issues with using more then 1 field as a primary key.

Now onto my complaint. While I'm willing to be called a newbie at ASP.NET and will bow to anyone who can show me the proper way to use OLDValues, this problem appears to be a bug in the GridViews control or SqlDataSource control. If I had the source code for these controls I would debug them for Microsoft. To summorize the issue...

1) Create a table with 2 fields as the primary key.
2) Add an SqlDataSource control for the table to an ASP.NET page. Be sure to add SQL code for the Insert, Update, Delete methods with parameters that correspond to the OldValuesParameterFormatString.
3) Add a GridView to the ASP.NET page and set its datasource to the SqlDataSource control.
4) In the Insert, Update, and Delete queries of the SqlDatasource control, bind the old value parameters to the GridView's SelectedValue.
5) Add a label to the ASP.NET page.
6) Add a Button to the ASP.NET page.
7) In the Button click procedure add code to update the record and execute the update method of the SqlDataSource. i.e. Sqldatasource1.updateParameters.item(0).defaultValue = "new value". updatedCount = Sqldatasource1.Update().
8) Display the updated count in the label.

When you test the page you'll find that 0 records are updated. This problem seesm to stem from the fact that only the first column of the GridView is used as the selectedValue. Meaning both key fields for the database table have been set to the first column's value of the GridView. Its almost enough to make me start a blog! lol Good luck people :)

Johnny

|||

what is the tbProductName...

is it the id of the some control.