DB2OLEDB issue - possible bug

I have several SSIS packages where I am connecting to a DB2 catalog to grab data on a nightly basis. These packages drop/create and reload data from DB2 to both my 2005, 32 bit and 64 bit servers. In the Connection Properties of the packages that reside on my 64 bit server, I am noticing a quirk in setting the password for the DB2 catalog. If I go in and manually type in the password and test the connection, everything is fine. The package will run either right there through Debug Mode, or at the next run time, without any issues. However, as soon as it runs the very next time, the password for the DB2 catalog reverts back to a blank password and the package will fail when it runs the next/second time. I have unchecked the Blank Password box and checked the Allow For Saved Password box on BOTH the Data Link page and the Connection Properties page. But, this does not resolve the issue. It is almost as if it is ignoring the Allow For Saved Password.

The packages are running fine in the 32 bit environment, by the way. I am only experiencing this issue on the 64 bit server.

Thnx in advance, for any suggestions or answers.

ScottyeOK... never mind... I figured it out. You have to set the RetainSameConnection value from False to True in the properties of the connection.

DB2OLEDB issue - possible bug

I have several SSIS packages where I am connecting to a DB2 catalog to grab data on a nightly basis. These packages drop/create and reload data from DB2 to both my 2005, 32 bit and 64 bit servers. In the Connection Properties of the packages that reside on my 64 bit server, I am noticing a quirk in setting the password for the DB2 catalog. If I go in and manually type in the password and test the connection, everything is fine. The package will run either right there through Debug Mode, or at the next run time, without any issues. However, as soon as it runs the very next time, the password for the DB2 catalog reverts back to a blank password and the package will fail when it runs the next/second time. I have unchecked the Blank Password box and checked the Allow For Saved Password box on BOTH the Data Link page and the Connection Properties page. But, this does not resolve the issue. It is almost as if it is ignoring the Allow For Saved Password.

The packages are running fine in the 32 bit environment, by the way. I am only experiencing this issue on the 64 bit server.

Thnx in advance, for any suggestions or answers.

ScottyeOK... never mind... I figured it out. You have to set the RetainSameConnection value from False to True in the properties of the connection.

db_ddladmin role without 'drop' capability

Hello -
I'm looking for a way to assign a user db_ddladmin
permissions on a particular database, but without the DROP
functionality. I want them to be able to do everything
that the role entails, but not to be able to drop tables
etc. Any help would be greatly appreciated...thanks!
MatthewHi,
If you provide db_ddladmin role you cant restrict the user from drop
command. Because the DENY or REVOKE
command can not be granted.
So alternative is provide the roles 'db_datareader', 'db_datawriter' the
user and provide explicit grant to
create table,create proc,create function,create view.
Sample:-
sp_addrolemember 'db_datareader','user'
go
sp_addrolemember 'db_datawriter','user'
go
grant create table,create proc,create function,create view to <user>
Thanks
Hari
MCDBA
"Matthew" <anonymous@.discussions.microsoft.com> wrote in message
news:017601c46dd3$52520310$a301280a@.phx.gbl...
> Hello -
> I'm looking for a way to assign a user db_ddladmin
> permissions on a particular database, but without the DROP
> functionality. I want them to be able to do everything
> that the role entails, but not to be able to drop tables
> etc. Any help would be greatly appreciated...thanks!
> Matthew|||Thanks Hari, much appreciated!

>--Original Message--
>Hi,
>If you provide db_ddladmin role you cant restrict the
user from drop
>command. Because the DENY or REVOKE
>command can not be granted.
>So alternative is provide the
roles 'db_datareader', 'db_datawriter' the
>user and provide explicit grant to
>create table,create proc,create function,create view.
>Sample:-
>sp_addrolemember 'db_datareader','user'
>go
>sp_addrolemember 'db_datawriter','user'
>go
>grant create table,create proc,create function,create
view to <user>
>--
>Thanks
>Hari
>MCDBA
>"Matthew" <anonymous@.discussions.microsoft.com> wrote in
message
>news:017601c46dd3$52520310$a301280a@.phx.gbl...
DROP[vbcol=seagreen]
>
>.
>

db rights

Hi!
How can I find out what privileges a user has on the database?
I want to make sure the user has enough privileges to drop/create
tables before I try to do this in my application.
I should be able to see what rights the user has on the current database,
but
I can't get SP_TABLE_PRIVILEGES to do what I want (or anything at all for
that matter), as I want to know the privileges the user has on the DATABASE,
not TABLE
I tried browsing the system tables as well but I can't find a way to
determine if a user is allowed to drop/create tables.
Greatful for any help!
/ PeterIn T-SQL you could use the PERMISSIONS system function to check the current
user's permissions :
http://msdn.microsoft.com/library/d...br />
6f78.asp
E.g.:
if (permissions() & 2 = 2)
begin
print 'Can create table'
end
else
begin
print 'Cannot create table'
end
Also look up sp_helprotect in Books Online.
In SQL-DMO you can see permissions given to a user with the
ListDatabasePermissions method of the User object:
http://msdn.microsoft.com/library/d...r />
_7mhw.asp
ML
http://milambda.blogspot.com/

DB name changes daily

Hi,
I am trying to write SQL automation that runs SQL scripts against the daily drop of the database. Each day, a new DB is created using the format (Testdb + xxxxx )where xxxxx= computed build number. Thus today's db name might be TEST12345 and tomorrow wo
uld be Test12346. I have a script that creates the dbname such as Test12345 and when I run it, I get the expected result, but when I try this statement
Use @.dbname
I get an error.
Is there a way to introduce a variable with a Use statement so that I can automatically open the latest db?
Thanks
Hi,
'USE' statement will change the DB context for the current connection and
then go back to where it was before (Default datbase context).
Doing this in an exec will change the context until the exec is completed
and then go back to default database context.
This is small example to show how to do it. This example uses the Northwind
database.
This database has a stored procedure 'emp_list' . From the master database
and execute the following:-
set quoted_identifier off
declare @.dbname varchar(15), @.procname varchar(25)
declare @.startdate varchar(10) , @.enddate varchar(10)
set @.dbname = 'northwind'
set @.procname = '[emp_list]'
exec ('USE ' + @.dbname + ' execute ' + @.procname )
the last statement translates to:
exec ( USE northwind execute [emp_list])
But after the execution again the context will go to the default database.
Note:
The best option rather than using the above is:- (SP_DEFAULTDB)
1. Have a common login
2. Every time after creating the new database use the sp_defaultdb procedure
to change the default db of the login
3. After this when ever that user logins the database context will be the
new database and there is nolt required to give USE XXXXX
Thanks
Hari
MCDBA
"Tomas" <anonymous@.discussions.microsoft.com> wrote in message
news:F9E1B6D0-5378-45AD-8A2C-6DF194B86B0A@.microsoft.com...
> Hi,
> I am trying to write SQL automation that runs SQL scripts against the
daily drop of the database. Each day, a new DB is created using the format
(Testdb + xxxxx )where xxxxx= computed build number. Thus today's db name
might be TEST12345 and tomorrow would be Test12346. I have a script that
creates the dbname such as Test12345 and when I run it, I get the expected
result, but when I try this statement
> Use @.dbname
> I get an error.
> Is there a way to introduce a variable with a Use statement so that I can
automatically open the latest db?
> Thanks

DB Growth Issue

I have done this experiment on one of the tables. There is table called
build havinf nvText Field with large no of records. I want to drop that
column and recover space. These are the results I got.

SP_SPACEUSED BUILD Results
name rows reserved data index_size unused

1. Before Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB

2. After Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB

3. After Executing the Shrink Database from Enterprise Manager.
Build 663211 608280 KB 604744 KB 3456 KB 80 K

4. After Executing DBCC DBReindex (build,'',70)

Build 663211 124096 KB 123392 KB 712 KB -8 KB

Can anyone please explain me after executing step 3 i.e shrink data
column as well as index_size shows an increased figure whereas logically
it should be a reduced figure.

Regards,
Harcharan

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Harcharan Jassal <hjjassal@.yahoo.com> wrote in message news:<41218aa5$0$14438$c397aba@.news.newsgroups.ws>...
> I have done this experiment on one of the tables. There is table called
> build havinf nvText Field with large no of records. I want to drop that
> column and recover space. These are the results I got.
> SP_SPACEUSED ?BUILD? Results
> name rows reserved data index_size unused
> 1. Before Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 2. After Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 3. After Executing the Shrink Database from Enterprise Manager.
> Build 663211 608280 KB 604744 KB 3456 KB 80 K
> 4. After Executing DBCC DBReindex (build,'',70)
> Build 663211 124096 KB 123392 KB 712 KB -8 KB
> Can anyone please explain me after executing step 3 i.e shrink data
> column as well as index_size shows an increased figure whereas logically
> it should be a reduced figure.
> Regards,
> Harcharan
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

As Books Online mentions, in some situations the data returned by
sp_spaceused "may not be current". You can try using @.updateusage to
see if that returns correct information:

exec sp_spaceused 'build', 'true'

See the Remarks section of sp_spaceused and also DBCC UPDATEUSAGE in
Books Online.

Simon