Altering a connection manager dynamically via a variable

Within an SSIS Package, we are trying to change the connection string of an output file connection manager at runtime (used for package logging).

To do this, we have defined variables with package-level scope and set the connection manager connection string to this variable. The first step of the package is to set these variables. The second step begins the rest of the package operations (moving data). The package executes successfully, but the log file is never created/appended to.

When the package is debugged, I can verify that the variables are being set correctly in the script task and that the variable values are being passed to the connection manager data sources.

Any ideas why this isn’t working?

You shouldn't use script tasks to try and change connection manager connection strings. Use this technique: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

-Jamie

|||Use the mthods in Jamie link that is how I do mine and it works great.

Altering (or recreating) a Stored Procedure "header"

We are using SQL Server 2005 to develop a simple SP. We started by including an output parameter which would report back the identity of the record being inserted or updated. We have since been trying to drop and recreate the SP without the output parameter, or alter the SP with the same outcome in mind. Neither has been succeeding, as confirmed by inspection of the sys.objects and sys.parameters tables. What might we be missing? We are using the Developer Edition, which may or may not be adequate to the task. Or maybe earlier versions of SQL Server are more robust and would be more successful to help us succeed? Please advise. Thank you.Could you please explain how you are recreating the SP? If you are doing it from the UI or something then you may want to post this question in the Tools forum. Otherwise, please post the DDL statement(s) and the reprot steps.|||I believe I see what we were (or in this case weren't) doing... The USE statement is necessary to point the scripts to the correct database. We were seeing the outcome of confusing the master database with our application database. Thanks much for anyone stopping to consider our "dilemma".|||In essence, we are checking for existence of the stored procedure in the system table first, I believe sys.objects. If we find it there first, we drop it. Then we follow up by recreating it. But, as I mentioned in a follow up to our original post, the issue turned out to be a case of not using the USE statement. So what I thought was showing up in our application database was actually showing up in the master database. Not quite what we were shooting for. So hence the confusion.

allocated, unused and free space

Considering the output of sp_spaceused (below) on my SQL Server 2005
database, is it possible to get back (release to the OS) the UNUSED space,
shrinking does not do it.
Basically I need to resize this database, most of its space is not being
used and is filling up my backup storage.
I have plenty of room in my data drive but I keep serveral days worth of
backups and thouse GB start to add up and fill up my backup drive.
Of the 28 GB only about 4 GB are being used, the rest is just RESERVED.
database_name: c8audit
database_size: 26960.63 MB
unallocated space: 2033.20 MB
reserved: 25521840 KB
data: 3561528 KB
index_size: 8440 KB
unused: 21951872 KB
Thanks,
Tim
DBCC SHRINKFILE will return the space to the OS as long as it is able to
shrink it in the first place. Can you try running that command and post the
actual command you used with all the parameters along with the output from
it? The backups do not include "free space" and only backup the actual
data. So if you have 4GB of data your backups should be roughly 4GB
regardless of how large your db is overall.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tim Bales" <timbales@.bellsouth.net> wrote in message
news:OXgzVobbIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Considering the output of sp_spaceused (below) on my SQL Server 2005
> database, is it possible to get back (release to the OS) the UNUSED space,
> shrinking does not do it.
> Basically I need to resize this database, most of its space is not being
> used and is filling up my backup storage.
> I have plenty of room in my data drive but I keep serveral days worth of
> backups and thouse GB start to add up and fill up my backup drive.
> Of the 28 GB only about 4 GB are being used, the rest is just RESERVED.
> database_name: c8audit
> database_size: 26960.63 MB
> unallocated space: 2033.20 MB
> reserved: 25521840 KB
> data: 3561528 KB
> index_size: 8440 KB
> unused: 21951872 KB
> Thanks,
> Tim
>
|||Hi Tim
There is a difference between allocated and unused space. Unallocated space
could be returned to the OS when you shrink a file, but the unused space is
space that has already been allocated to an object, but just doesn't yet
have any data stored in it.
Without seeing you table definitions and knowing the kind of data you're
storing, it's hard to know why you have so much unused space in your tables.
You might try running sp_spaceused on each table, and see which ones have
the most unused.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tim Bales" <timbales@.bellsouth.net> wrote in message
news:OXgzVobbIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Considering the output of sp_spaceused (below) on my SQL Server 2005
> database, is it possible to get back (release to the OS) the UNUSED space,
> shrinking does not do it.
> Basically I need to resize this database, most of its space is not being
> used and is filling up my backup storage.
> I have plenty of room in my data drive but I keep serveral days worth of
> backups and thouse GB start to add up and fill up my backup drive.
> Of the 28 GB only about 4 GB are being used, the rest is just RESERVED.
> database_name: c8audit
> database_size: 26960.63 MB
> unallocated space: 2033.20 MB
> reserved: 25521840 KB
> data: 3561528 KB
> index_size: 8440 KB
> unused: 21951872 KB
> Thanks,
> Tim
>
|||Thanks all for your help.
Using SQL Server 2005's standard report "Disk Usage by Table" I was able to
find out that about 90% of the unused space has been allocated to ONE table.
Among this table's fields there are two VARCHAR that caught my attention
because their lengths are bigger than normal, one is 1024 the other one is
2000.
The table has about 2 million records and both of these fields are only
storing a few characters, even though I have not done any calculations I
believe the free space is in these almost empty fields.
I can't modify the fields' length, but about 90% of the records on this
table can go, so I'm going to delete the records to regain the space.
I did this in TEST after my original post and it worked, the UNUSED space
became AVAILABLE FREE and shrinking released it.
Again THANKS!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eyBNF$ebIHA.4208@.TK2MSFTNGP04.phx.gbl...
> Hi Tim
> There is a difference between allocated and unused space. Unallocated
> space could be returned to the OS when you shrink a file, but the unused
> space is space that has already been allocated to an object, but just
> doesn't yet have any data stored in it.
> Without seeing you table definitions and knowing the kind of data you're
> storing, it's hard to know why you have so much unused space in your
> tables. You might try running sp_spaceused on each table, and see which
> ones have the most unused.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tim Bales" <timbales@.bellsouth.net> wrote in message
> news:OXgzVobbIHA.4880@.TK2MSFTNGP03.phx.gbl...
>
|||Hi Tim
I'm glad you were able to accomplish your goal.
However, it's a mystery what the cause of all the unused space was. A
varchar column should not reserve more space than it actually uses. So if
there were only a few characters in them, that should not have caused excess
space allocation. It might have been that there was padding with spaces, or
it might have been that the columns were longer originally and then updated.
But if you've deleted the rows, we'll probably never know. ;-)
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tim Bales" <timbales@.bellsouth.net> wrote in message
news:%23SIRI7kbIHA.1376@.TK2MSFTNGP02.phx.gbl...
> Thanks all for your help.
> Using SQL Server 2005's standard report "Disk Usage by Table" I was able
> to find out that about 90% of the unused space has been allocated to ONE
> table.
> Among this table's fields there are two VARCHAR that caught my attention
> because their lengths are bigger than normal, one is 1024 the other one is
> 2000.
> The table has about 2 million records and both of these fields are only
> storing a few characters, even though I have not done any calculations I
> believe the free space is in these almost empty fields.
> I can't modify the fields' length, but about 90% of the records on this
> table can go, so I'm going to delete the records to regain the space.
> I did this in TEST after my original post and it worked, the UNUSED space
> became AVAILABLE FREE and shrinking released it.
> Again THANKS!
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eyBNF$ebIHA.4208@.TK2MSFTNGP04.phx.gbl...
>

allocated, unused and free space

Considering the output of sp_spaceused (below) on my SQL Server 2005
database, is it possible to get back (release to the OS) the UNUSED space,
shrinking does not do it.
Basically I need to resize this database, most of its space is not being
used and is filling up my backup storage.
I have plenty of room in my data drive but I keep serveral days worth of
backups and thouse GB start to add up and fill up my backup drive.
Of the 28 GB only about 4 GB are being used, the rest is just RESERVED.
database_name: c8audit
database_size: 26960.63 MB
unallocated space: 2033.20 MB
reserved: 25521840 KB
data: 3561528 KB
index_size: 8440 KB
unused: 21951872 KB
Thanks,
TimDBCC SHRINKFILE will return the space to the OS as long as it is able to
shrink it in the first place. Can you try running that command and post the
actual command you used with all the parameters along with the output from
it? The backups do not include "free space" and only backup the actual
data. So if you have 4GB of data your backups should be roughly 4GB
regardless of how large your db is overall.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tim Bales" <timbales@.bellsouth.net> wrote in message
news:OXgzVobbIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Considering the output of sp_spaceused (below) on my SQL Server 2005
> database, is it possible to get back (release to the OS) the UNUSED space,
> shrinking does not do it.
> Basically I need to resize this database, most of its space is not being
> used and is filling up my backup storage.
> I have plenty of room in my data drive but I keep serveral days worth of
> backups and thouse GB start to add up and fill up my backup drive.
> Of the 28 GB only about 4 GB are being used, the rest is just RESERVED.
> database_name: c8audit
> database_size: 26960.63 MB
> unallocated space: 2033.20 MB
> reserved: 25521840 KB
> data: 3561528 KB
> index_size: 8440 KB
> unused: 21951872 KB
> Thanks,
> Tim
>|||Hi Tim
There is a difference between allocated and unused space. Unallocated space
could be returned to the OS when you shrink a file, but the unused space is
space that has already been allocated to an object, but just doesn't yet
have any data stored in it.
Without seeing you table definitions and knowing the kind of data you're
storing, it's hard to know why you have so much unused space in your tables.
You might try running sp_spaceused on each table, and see which ones have
the most unused.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tim Bales" <timbales@.bellsouth.net> wrote in message
news:OXgzVobbIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Considering the output of sp_spaceused (below) on my SQL Server 2005
> database, is it possible to get back (release to the OS) the UNUSED space,
> shrinking does not do it.
> Basically I need to resize this database, most of its space is not being
> used and is filling up my backup storage.
> I have plenty of room in my data drive but I keep serveral days worth of
> backups and thouse GB start to add up and fill up my backup drive.
> Of the 28 GB only about 4 GB are being used, the rest is just RESERVED.
> database_name: c8audit
> database_size: 26960.63 MB
> unallocated space: 2033.20 MB
> reserved: 25521840 KB
> data: 3561528 KB
> index_size: 8440 KB
> unused: 21951872 KB
> Thanks,
> Tim
>|||Thanks all for your help.
Using SQL Server 2005's standard report "Disk Usage by Table" I was able to
find out that about 90% of the unused space has been allocated to ONE table.
Among this table's fields there are two VARCHAR that caught my attention
because their lengths are bigger than normal, one is 1024 the other one is
2000.
The table has about 2 million records and both of these fields are only
storing a few characters, even though I have not done any calculations I
believe the free space is in these almost empty fields.
I can't modify the fields' length, but about 90% of the records on this
table can go, so I'm going to delete the records to regain the space.
I did this in TEST after my original post and it worked, the UNUSED space
became AVAILABLE FREE and shrinking released it.
Again THANKS!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eyBNF$ebIHA.4208@.TK2MSFTNGP04.phx.gbl...
> Hi Tim
> There is a difference between allocated and unused space. Unallocated
> space could be returned to the OS when you shrink a file, but the unused
> space is space that has already been allocated to an object, but just
> doesn't yet have any data stored in it.
> Without seeing you table definitions and knowing the kind of data you're
> storing, it's hard to know why you have so much unused space in your
> tables. You might try running sp_spaceused on each table, and see which
> ones have the most unused.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tim Bales" <timbales@.bellsouth.net> wrote in message
> news:OXgzVobbIHA.4880@.TK2MSFTNGP03.phx.gbl...
>> Considering the output of sp_spaceused (below) on my SQL Server 2005
>> database, is it possible to get back (release to the OS) the UNUSED
>> space, shrinking does not do it.
>> Basically I need to resize this database, most of its space is not being
>> used and is filling up my backup storage.
>> I have plenty of room in my data drive but I keep serveral days worth of
>> backups and thouse GB start to add up and fill up my backup drive.
>> Of the 28 GB only about 4 GB are being used, the rest is just RESERVED.
>> database_name: c8audit
>> database_size: 26960.63 MB
>> unallocated space: 2033.20 MB
>> reserved: 25521840 KB
>> data: 3561528 KB
>> index_size: 8440 KB
>> unused: 21951872 KB
>> Thanks,
>> Tim
>|||Hi Tim
I'm glad you were able to accomplish your goal.
However, it's a mystery what the cause of all the unused space was. A
varchar column should not reserve more space than it actually uses. So if
there were only a few characters in them, that should not have caused excess
space allocation. It might have been that there was padding with spaces, or
it might have been that the columns were longer originally and then updated.
But if you've deleted the rows, we'll probably never know. ;-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tim Bales" <timbales@.bellsouth.net> wrote in message
news:%23SIRI7kbIHA.1376@.TK2MSFTNGP02.phx.gbl...
> Thanks all for your help.
> Using SQL Server 2005's standard report "Disk Usage by Table" I was able
> to find out that about 90% of the unused space has been allocated to ONE
> table.
> Among this table's fields there are two VARCHAR that caught my attention
> because their lengths are bigger than normal, one is 1024 the other one is
> 2000.
> The table has about 2 million records and both of these fields are only
> storing a few characters, even though I have not done any calculations I
> believe the free space is in these almost empty fields.
> I can't modify the fields' length, but about 90% of the records on this
> table can go, so I'm going to delete the records to regain the space.
> I did this in TEST after my original post and it worked, the UNUSED space
> became AVAILABLE FREE and shrinking released it.
> Again THANKS!
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eyBNF$ebIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi Tim
>> There is a difference between allocated and unused space. Unallocated
>> space could be returned to the OS when you shrink a file, but the unused
>> space is space that has already been allocated to an object, but just
>> doesn't yet have any data stored in it.
>> Without seeing you table definitions and knowing the kind of data you're
>> storing, it's hard to know why you have so much unused space in your
>> tables. You might try running sp_spaceused on each table, and see which
>> ones have the most unused.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Tim Bales" <timbales@.bellsouth.net> wrote in message
>> news:OXgzVobbIHA.4880@.TK2MSFTNGP03.phx.gbl...
>> Considering the output of sp_spaceused (below) on my SQL Server 2005
>> database, is it possible to get back (release to the OS) the UNUSED
>> space, shrinking does not do it.
>> Basically I need to resize this database, most of its space is not being
>> used and is filling up my backup storage.
>> I have plenty of room in my data drive but I keep serveral days worth of
>> backups and thouse GB start to add up and fill up my backup drive.
>> Of the 28 GB only about 4 GB are being used, the rest is just RESERVED.
>> database_name: c8audit
>> database_size: 26960.63 MB
>> unallocated space: 2033.20 MB
>> reserved: 25521840 KB
>> data: 3561528 KB
>> index_size: 8440 KB
>> unused: 21951872 KB
>> Thanks,
>> Tim
>>
>

ALL STORED PROCS RETURNING NOTHING BUT NULLS IN OUTPUT PARAMETERS

I think my hosting company changed something on SQL Server, but I don't
know what. Suddenly, all OUTPUT parameters from ANY SPROC return NULL.
The funny thing is, the values are in the variables within the SPROCS
(see the PRINT results on the SPROC example below) The behavior is the
same in Query Analyzer as in ADO.Net.
Any help would be GREATLY appreciated.
Thanks,
Brad
Here is some detail:
Stored Proc Definition
=====================================
ALTER PROC DBO.USP_GET_POOL_INFO
@.POOLID INT
,@.PRIVATE CHAR(1) OUTPUT
,@.PLAYERLIMIT NVARCHAR(33) OUTPUT
,@.PLAYERCOUNT INT OUTPUT
AS
SELECT
@.PRIVATE = private
,@.PLAYERLIMIT = CASE WHEN p.player_limit = 0 OR p.player_limit IS
NULL THEN 'No Limit' ELSE CAST(p.player_limit AS NVARCHAR(33)) END
,@.PLAYERCOUNT = ISNULL(pc.player_count,'')
from
tPools p
LEFT OUTER JOIN
(
SELECT
POOLID,
COUNT(*) player_count
FROM
tProfilePool
GROUP BY
POOLID
) pc
ON
p.pool_id = pc.poolid
where
pool_id = @.POOLID
PRINT @.PRIVATE
PRINT @.PLAYERLIMIT
PRINT @.PLAYERCOUNT
========================================
====
Stored Proc Execution:
========================================
====
DECLARE @.PLAYERLIMIT NVARCHAR(33)
DECLARE @.PLAYERCOUNT INT
DECLARE @.PRIVATE CHAR(1)
EXEC DBO.USP_GET_POOL_INFO 2, @.PRIVATE, @.PLAYERLIMIT, @.PLAYERCOUNT
SELeCT @.PRIVATE, @.PLAYERLIMIT, @.PLAYERCOUNT
Result:
NULL NULL NULL
Messages Result (from the PRINT command):
Y
No Limit
1
========================================
Raw TSQL Approach:
========================================
====
DECLARE @.PRIVATE CHAR(1)
DECLARE @.PLAYERLIMIT VARCHAR(33)
DECLARE @.PLAYERCOUNT INT
SELECT
@.PRIVATE = private
,@.PLAYERLIMIT = CASE WHEN p.player_limit = 0 OR p.player_limit IS
NULL THEN 'No Limit' ELSE CAST(p.player_limit AS NVARCHAR(33)) END
,@.PLAYERCOUNT = ISNULL(pc.player_count,'')
from
tPools p
LEFT OUTER JOIN
(
SELECT
POOLID,
COUNT(*) player_count
FROM
tProfilePool
GROUP BY
POOLID
) pc
ON
p.pool_id = pc.poolid
where
pool_id = 2
SELECT
@.PRIVATE, @.PLAYERLIMIT, @.PLAYERCOUNT
========================================
Execution Result:
Y No Limit 1You need to specify OUTPUT in the EXEC
EXEC DBO.USP_GET_POOL_INFO 2, @.PRIVATE OUTPUT , @.PLAYERLIMIT OUTPUT ,
@.PLAYERCOUNT OUTPUT