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.

OLE DB Connection Manager will not save password

I'm stuck with a very annoying problem - any help would be greatly appreciated.

I created a package using Business Intelligence Project. The package reads from a Flat File source and saves to a SQL Server table.

The package has a Data Source for the database connection. In this I have opted to save the password.

When I run the package in VS on my machine it works fine.

I then deployed the package to our SQL 2005 database server using the Deployment Utility. It then appeared in the MSDB section.

I then tested the package ran from my machine using Management Studio and all was fine.

However, if I try to run the package from the database server itself I run into problems. Specifically, the log gives me the following error:
"The AcquireConnection method call to the connection manager "<My Database>" failed with error code 0xC0202009"
....and further down I get:
"Error: an OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source "Microsoft SQL Native Client" Hresult 0x80040E4D Description: "Login failed for user 'sa'.".

What seems to be happening is the password is not being saved in the connection string. Sure enough, if I look at the Connection Managers section of the Execute Package Utility in Management Studio, then my database connection manager has only a User ID specified and no "pwd=". If I add "pwd=<my password>" to this connection string here then the package works.

How do I get this password to be remembered by the Package?

Incidentally, I have also tried writing a VB.NET program to call the package programmatically. Using this, I have tried to set the connection string of my Data Source in the code to try to get round the problem above. However, even after this I still find the only machine the package runs on is mine, and everywhere else it fails to connect.

Please help! Thanks in advance.

Richard FPlease search the forums. SSIS does not save sensitive information in the packages. You'll need to look at using the /SET command line option to pass in a password.|||Thanks for the swift reply.

Apologies for not checking the forums sufficiently before posting. Anyway I have the answer now.

For anybody else struggling with a similar problem, here is your solution. It involves using XML Package Configurations:
http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.htm

It has to be said in my defence that the MSDN help on SSIS makes no mention of potential deployment problems with regard to connection strings and passwords.
Nowhere is it made clear that using Package Configurations is the answer AND

the password needs to be manually added to the XML after creating the

config file.

It seems intuitive that the "Save Password" box you tick in when creating the Connection Manager in Visual Studio should do just that, but this is a red herring.
This, along with the fact that DTS didn't have such problems, has clearly led to quite a few people getting caught out.

Thanks anyway.

RichardF|||

fagster wrote:

Thanks for the swift reply.

Apologies for not checking the forums sufficiently before posting. Anyway I have the answer now.

For anybody else struggling with a similar problem, here is your solution. It involves using XML Package Configurations:
http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.htm

It has to be said in my defence that the MSDN help on SSIS makes no mention of potential deployment problems with regard to connection strings and passwords.
Nowhere is it made clear that using Package Configurations is the answer AND

the password needs to be manually added to the XML after creating the

config file.

It seems intuitive that the "Save Password" box you tick in when creating the Connection Manager in Visual Studio should do just that, but this is a red herring.
This, along with the fact that DTS didn't have such problems, has clearly led to quite a few people getting caught out.

Thanks anyway.

RichardF

Microsoft DOES NOT WANT to be in the business of saving sensitive information. Hence the reason they designed it the way they did.

As soon as SSIS touches that config file, you'll find that the password will get removed -- even if you manually entered it. The work around is to set that file to read only, I guess.

Saving passwords is generally a bad idea anyway, given other, better means such as single sign-on, pass-through authentication, etc...|||

Well they did save such information in DTS, this is my point. Then didn't tell us how to do what was elementary in DTS using SSIS. So anyone who uses SQL Authentication and who wants to deploy a simple package to import say some flat file data to their server is going to run into these problems it seems to me.

So could you possibly point me in the direction of these other better methods that don't involve having a connection string with a password? We don't want to move away form SQL Authentication for our database server though if that's what is required with these methods.

Thanks again for your assistance Phil.

RichardF

|||

fagster wrote:

Well they did save such information in DTS, this is my point. Then didn't tell us how to do what was elementary in DTS using SSIS. So anyone who uses SQL Authentication and who wants to deploy a simple package to import say some flat file data to their server is going to run into these problems it seems to me.

So could you possibly point me in the direction of these other better methods that don't involve having a connection string with a password? We don't want to move away form SQL Authentication for our database server though if that's what is required with these methods.

Thanks again for your assistance Phil.

RichardF

Active Directory would be much better than using isolated user names in SQL Server.