changing the value of @join_unique_key on existing filter

I need to change a filter on an existing publication and need to change the
value of @.join_unique_key to 0 from 1. I can't find any documented way of
doing this, is there any way to do this? Thanks, Scott
use sp_helpmergearticle to get the filter name. Then use
sp_changemergefilter to change this option. Here is an example
sp_changemergefilter @.publication = 'pubs',@.article =
'titleauthor',@.filtername = 'titleauthor_authors', @.property
'join_unique_key', @.value = 'true', @.force_invalidate_snapshot=1,
@.force_reinit_subscription =1
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Scott Simons" <Scott.Simons.At.MealMagic.Com.Remove.This> wrote in message
news:6E5C0638-F986-47B0-8FE7-FD80C87FC5C5@.microsoft.com...
> I need to change a filter on an existing publication and need to change
the
> value of @.join_unique_key to 0 from 1. I can't find any documented way of
> doing this, is there any way to do this? Thanks, Scott

changing the sa password in SQL 2005

Hello, All
Is there a way to change the sa password in SQL 2005 without knowing the
existing password?
I tried everything I can find online to change the password but nothing
works.
This is on my test server so I am opened to anything.
Thank you.What is the error message you are getting? Are you connecting as a member of
sysadmin? Try connecting using Windows Authentication as a local
Administrator.
Ben Nevarez, MCDBA, OCP
Database Administrator
"steve" wrote:
> Hello, All
> Is there a way to change the sa password in SQL 2005 without knowing the
> existing password?
> I tried everything I can find online to change the password but nothing
> works.
> This is on my test server so I am opened to anything.
> Thank you.
>
>
>|||maybe if you are an administrator of SQL Server, you can change the password
of another account. (using the management console)
so if you use the local windows administrator account, then you can
certainly access the server as an administrtor, then you can change the
password.
but if your security model use only SQL accounts and you don't have another
account with enough privilege, then you can't change the password of another
account.
I hope this will help you
"steve" <steve@.nothing.com> wrote in message
news:uifjW6dpGHA.4196@.TK2MSFTNGP04.phx.gbl...
> Hello, All
> Is there a way to change the sa password in SQL 2005 without knowing the
> existing password?
> I tried everything I can find online to change the password but nothing
> works.
> This is on my test server so I am opened to anything.
> Thank you.
>
>|||Hi Steve,
Does this help?
EXEC sp_password NULL, 'newpass','sa'
Need to be a sysadmin to run this however.
--
David Tan,
Production DBA
Webcentral
www.webcentral.com.au
"steve" wrote:
> Hello, All
> Is there a way to change the sa password in SQL 2005 without knowing the
> existing password?
> I tried everything I can find online to change the password but nothing
> works.
> This is on my test server so I am opened to anything.
> Thank you.
>
>
>

Changing security authentication type.

Hi,
in an existing instance of SQL, will changing the security authentication
from SQL & Windows logins, to only windows logins cause any issues?
Also, if a database has been created with hardly any security already, are
there any issues with introducing security further down the line. For
example, a SQL server has been deployed already by my predecessor, with
authentication in SA & windows mode, and allowing pretty much anyone access
to SQL. If i was to create a DBA_Admin group and assign admin writes only to
that group, will it cause any problems in a already functioning database?
TIAInline...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Stu" <stu@.blah.com> wrote in message
news:%23E25Hpb1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> Hi,
> in an existing instance of SQL, will changing the security authentication
> from SQL & Windows logins, to only windows logins cause any issues?
>
It will block (lock out) any attempts to access the database using the sa
account (or any other SQL Login).

> Also, if a database has been created with hardly any security already, are
> there any issues with introducing security further down the line. For
There 'could' be substaintial disruption when you introduce security.
Consider a building where there were no keys necessary to enter the building
and the rooms, and suddenly keys were required to enter the building, and
also to enter rooms. Until everyone got all of the correct keys for their
needs, there would be major disruption. However, if this was a well planned
process, and appropraite keys were distributed before all the locks were
install, the disruption would be minimal if at all.

> example, a SQL server has been deployed already by my predecessor, with
> authentication in SA & windows mode, and allowing pretty much anyone
> access to SQL. If i was to create a DBA_Admin group and assign admin
> writes only to that group, will it cause any problems in a already
> functioning database?
Creating a domain/DBA_Admin group, providing that group login access to the
server, and also placing that group in the sysadmin server role, will not
cause any problems in a functioning database.
The problems will occur as you start locking down and removing permissions
from the sa account. And you 'should' do that. Applications should not be
using the sa account for database access since the sa account can do
'anything' with and to the server.

> TIA
>|||ok, many thanks for the reply.
So in short you're saying to keep the "mixed" authentication, but to secure
the server down, the best path to take is to create an AD DBA group, and
configure key users to be in that group?
furthermore, to leave the sa account's permissions well alone!
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23tasa7b1GHA.480@.TK2MSFTNGP06.phx.gbl...
> Inline...
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Stu" <stu@.blah.com> wrote in message
> news:%23E25Hpb1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> It will block (lock out) any attempts to access the database using the sa
> account (or any other SQL Login).
>
>
> There 'could' be substaintial disruption when you introduce security.
> Consider a building where there were no keys necessary to enter the
> building and the rooms, and suddenly keys were required to enter the
> building, and also to enter rooms. Until everyone got all of the correct
> keys for their needs, there would be major disruption. However, if this
> was a well planned process, and appropraite keys were distributed before
> all the locks were install, the disruption would be minimal if at all.
>
> Creating a domain/DBA_Admin group, providing that group login access to
> the server, and also placing that group in the sysadmin server role, will
> not cause any problems in a functioning database.
> The problems will occur as you start locking down and removing permissions
> from the sa account. And you 'should' do that. Applications should not be
> using the sa account for database access since the sa account can do
> 'anything' with and to the server.
>
>|||Actually, I recommend that you take a deliberative approach to removing all
usage of the sa account, possibly with the eventual goal of switching to
Windows authentication.
Your first steps, however, are to determine the impact on the applications
of changing the connection strings. For some applications, that will be
relatively easy, for others, a re-deploy may be required and that will take
some time, effort and coordination.
Create one or more logins for the applications. Create one or more roles for
each database. Give those roles access to the appropriate databases. give
those roles permissions to the tables, views, stored procedures and
functions required by the applications. Place the logins in the new roles.
DO NOT put these logins in the sysadmin role.
Change the connections strings for the applications to use the new logins.
You want to eventually remove any application usage of the sa account.
This is a goal, it may take some time to eventually put it into place.
But for now, take no action to change the sa account -until you understand
the implications, and have a plan to addresses any issues.
However, as long as the sa account password is readily known by developers
(and whomever else may know it), you really have no security for your
database. (And if the application is hacked, the hackers will have admin
privileges in the database.)
There may be other regulatory reasons that you need to tighten up the
security, i.e., HIPPA, SarBox, etc. The usage of the sa account will most
likely be flagged as a security audit failure.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Stu" <stu@.blah.com> wrote in message
news:uSCBYwc1GHA.4648@.TK2MSFTNGP04.phx.gbl...
> ok, many thanks for the reply.
> So in short you're saying to keep the "mixed" authentication, but to
> secure the server down, the best path to take is to create an AD DBA
> group, and configure key users to be in that group?
> furthermore, to leave the sa account's permissions well alone!
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23tasa7b1GHA.480@.TK2MSFTNGP06.phx.gbl...
>|||Many thanks for your advice to date Arnie. Could i just run some more
questions past the group.
After reading your reply, i'm thinking that yes in time the sa account will
be withdrawn from use.
I'm just trying to get my head around a plan of attack on how it will take
place.
The first step i want to take is to create a group for suitable DBA's to
administer the server.
I'm guessing that i'm also going to have to create a DBA Admin user, which
will effectively take the place of the SA account, which will not as you
mention be a member of the sysadmin group / role.
Is the switch over just as easy as selecting the windows authentication
radio button?
if so, if the changeover is made, if things go wrong, will the role back be
to retick mixed mode or would the initial changeover be not possible to
reverse?
also what changes will need to be done to the individual databases, just
changing the connection strings?
once again, thanks for any further advice.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23LVeCAd1GHA.4116@.TK2MSFTNGP02.phx.gbl...
> Actually, I recommend that you take a deliberative approach to removing
> all usage of the sa account, possibly with the eventual goal of switching
> to Windows authentication.
> Your first steps, however, are to determine the impact on the applications
> of changing the connection strings. For some applications, that will be
> relatively easy, for others, a re-deploy may be required and that will
> take some time, effort and coordination.
> Create one or more logins for the applications. Create one or more roles
> for each database. Give those roles access to the appropriate databases.
> give those roles permissions to the tables, views, stored procedures and
> functions required by the applications. Place the logins in the new roles.
> DO NOT put these logins in the sysadmin role.
> Change the connections strings for the applications to use the new logins.
> You want to eventually remove any application usage of the sa account.
> This is a goal, it may take some time to eventually put it into place.
> But for now, take no action to change the sa account -until you understand
> the implications, and have a plan to addresses any issues.
> However, as long as the sa account password is readily known by developers
> (and whomever else may know it), you really have no security for your
> database. (And if the application is hacked, the hackers will have admin
> privileges in the database.)
> There may be other regulatory reasons that you need to tighten up the
> security, i.e., HIPPA, SarBox, etc. The usage of the sa account will most
> likely be flagged as a security audit failure.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Stu" <stu@.blah.com> wrote in message
> news:uSCBYwc1GHA.4648@.TK2MSFTNGP04.phx.gbl...
>|||Inline...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Stu" <stu@.blah.com> wrote in message
news:uZXUeon1GHA.3908@.TK2MSFTNGP05.phx.gbl...
> Many thanks for your advice to date Arnie. Could i just run some more
> questions past the group.
> After reading your reply, i'm thinking that yes in time the sa account
> will be withdrawn from use.
> I'm just trying to get my head around a plan of attack on how it will take
> place.
> The first step i want to take is to create a group for suitable DBA's to
> administer the server.
> I'm guessing that i'm also going to have to create a DBA Admin user, which
> will effectively take the place of the SA account, which will not as you
> mention be a member of the sysadmin group / role.
>
A new DBA Admin User login would only be useful for SQL Login purposes.
You're moving away from SQL Logins.
It may be useful to create a domain group that contains those appointed to
be the SQL Administrators. You can then assign that domain group to the
[sysadmin] role in the server. Anyone that is a SQL Administrator must b
e in
the sysadmin role. (It has the same 'power' as [sa], but the security yo
u
are moving toward is that a user must have their own domain account/password
in order to get into the server, and then if they are in the sysadmin role
they are allowed administrator priviledges. If you cannot create (or have
created) a domain group, then you could add each prospective administrator's
domain/username to the sysadmin role.
Also, if appropriate you can add users to the db_owner role in a database.
That role allows complete control of a database, create tables, etc., BUT
does not allow similar control for other databases on the same server. This
can be good for developers when a database is in development, and then
removed from developers when the database goes into production.

> Is the switch over just as easy as selecting the windows authentication
> radio button?
> if so, if the changeover is made, if things go wrong, will the role back
> be to retick mixed mode or would the initial changeover be not possible to
> reverse?
Yes, you can check the button for Windows Authentication, and if things
break, click on SQL Authentication to return it back to mixed mode. -It will
take a few seconds as the server has to stop/restart.

> also what changes will need to be done to the individual databases, just
> changing the connection strings?
>
Each application will have to have it's connection strings changed to allow
for the new security model. BUT first, you will have created a database role
for the application users, and added the domain groups/accounts to that
role.
It will also be necessary to go through each database and provide
appropriate permissions to that role for Tables, Views, Stored Procedures,
Functions.
Different applications that have their own databases 'should' each have a
distinct database role, so that a user in permissions in one database cannot
inadvertently access data in another database.

> once again, thanks for any further advice.
No problem.
For a good introduction into SQL Server security, see if you can find a copy
of Morris Lewis' book, SQL Server Security Distilled, ISBN 1-9043-47-07-X
http://www.abebooks.com/servlet/Boo...isbn=190434707X

>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23LVeCAd1GHA.4116@.TK2MSFTNGP02.phx.gbl...
>

Changing SA Password in SQL 2005

Hello, All
Is there a way to change the sa password in SQL 2005 without knowing the
existing password?
I tried everything I can find online to change the password but nothing
works.
This is on my test server so I am opened to anything.
Thank you."steve" <steve@.nothing.com> wrote in message
news:eFh7v7dpGHA.220@.TK2MSFTNGP05.phx.gbl...
> Hello, All
> Is there a way to change the sa password in SQL 2005 without knowing the
> existing password?
> I tried everything I can find online to change the password but nothing
> works.
> This is on my test server so I am opened to anything.
>
Connect to SQL Server as a sysadmin and change it.
alter login sa with password='Di55ab1eTh1sAcc0un7'
David

Changing ownership of an existing table?

Can the ownership of an existing SQL table be changed from the a user
to DBO? How? Sorry but I'm a novice.
Randy K
wawork@.dfw.wa.gov
Yes. Read up on sp_changedbowner within Books Online (within the SQL =
Server program group).
--=20
Keith
"Randy K" <wawork@.hotmail.com> wrote in message =
news:40aa55ca.8925156@.msnews.microsoft.com...
> Can the ownership of an existing SQL table be changed from the a user
> to DBO? How? Sorry but I'm a novice.
>=20
> Randy K
> wawork@.dfw.wa.gov
|||Keith,
I assume you meant sp_changeobjectowner.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:O25ILdQPEHA.3452@.TK2MSFTNGP10.phx.gbl...
Yes. Read up on sp_changedbowner within Books Online (within the SQL Server
program group).
Keith
"Randy K" <wawork@.hotmail.com> wrote in message
news:40aa55ca.8925156@.msnews.microsoft.com...
> Can the ownership of an existing SQL table be changed from the a user
> to DBO? How? Sorry but I'm a novice.
> Randy K
> wawork@.dfw.wa.gov
|||Yeah, read up on that one sp_changeobjectowner. sp_changeobjectowner =
will work a bit better than sp_changedbowner My fingers typed one =
thing while my brain was thinking something else.
Dinesh, Thanks for the correction!
--=20
Keith
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message =
news:uYnLtARPEHA.3944@.tk2msftngp13.phx.gbl...
> Keith,
>=20
> I assume you meant sp_changeobjectowner.
>=20
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
>=20
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:O25ILdQPEHA.3452@.TK2MSFTNGP10.phx.gbl...
> Yes. Read up on sp_changedbowner within Books Online (within the SQL =
Server[vbcol=seagreen]
> program group).
>=20
> --=20
> Keith
>=20
>=20
> "Randy K" <wawork@.hotmail.com> wrote in message
> news:40aa55ca.8925156@.msnews.microsoft.com...
user
>=20
>

changing logical file names

Using SS2000 SP4. I want to create a new test database from an existing
database backup. This is the sql that I use:
RESTORE DATABASE SMC_Test
FROM disk = '\\Depts\Technology\SQLServer\Backups\Colu\SMS\smc_936.bak'
WITH RECOVERY,
MOVE 'SMC_Data' TO 'S:\SQL\Data\SMCLMS_Test_Data.mdf',
MOVE 'SMC_Log' TO 'L:\SQL\Logs\SMCLMS_Test_Log.ldf'
Is there a way to change the logical file names at the same time? In another
post I saw this sql:
USE master
go
ALTER DATABASE XXX MODIFY FILE
( NAME='XX_old Name',NEWNAME='XX_New Name' )
go
Do I have to run that after I restore the database or is there another way?
Thanks,
--
Dan D.You have to do it after the restore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:1A8C3983-25E6-4762-A6D6-3C78BCC89114@.microsoft.com...
> Using SS2000 SP4. I want to create a new test database from an existing
> database backup. This is the sql that I use:
> RESTORE DATABASE SMC_Test
> FROM disk = '\\Depts\Technology\SQLServer\Backups\Colu\SMS\smc_936.bak'
> WITH RECOVERY,
> MOVE 'SMC_Data' TO 'S:\SQL\Data\SMCLMS_Test_Data.mdf',
> MOVE 'SMC_Log' TO 'L:\SQL\Logs\SMCLMS_Test_Log.ldf'
> Is there a way to change the logical file names at the same time? In another
> post I saw this sql:
> USE master
> go
> ALTER DATABASE XXX MODIFY FILE
> ( NAME='XX_old Name',NEWNAME='XX_New Name' )
> go
> Do I have to run that after I restore the database or is there another way?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Tibor Karaszi" wrote:
> You have to do it after the restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:1A8C3983-25E6-4762-A6D6-3C78BCC89114@.microsoft.com...
> > Using SS2000 SP4. I want to create a new test database from an existing
> > database backup. This is the sql that I use:
> >
> > RESTORE DATABASE SMC_Test
> > FROM disk = '\\Depts\Technology\SQLServer\Backups\Colu\SMS\smc_936.bak'
> > WITH RECOVERY,
> > MOVE 'SMC_Data' TO 'S:\SQL\Data\SMCLMS_Test_Data.mdf',
> > MOVE 'SMC_Log' TO 'L:\SQL\Logs\SMCLMS_Test_Log.ldf'
> >
> > Is there a way to change the logical file names at the same time? In another
> > post I saw this sql:
> > USE master
> > go
> > ALTER DATABASE XXX MODIFY FILE
> > ( NAME='XX_old Name',NEWNAME='XX_New Name' )
> > go
> >
> > Do I have to run that after I restore the database or is there another way?
> >
> > Thanks,
> > --
> > Dan D.
>
>

Changing Field name of an existing Table

Hi,
Is it possible to change any fieldname of an existing table?I mean to say
by TSQL statement.We know that we can alter the data type and width etc.
But I haven't got any info about filedname change.So if it is possible
Please help...
And Is there any TSQL command to alter multiple columns in a single statement?

Please help...
Thanks!!
Joydeepif it is not a primary or a foriegn key you can do something like so...

ALTER TABLE Mytable
ADD INQUIRYID2 BIGINT
GO
UPDATE MyTable SET INQUIRYID2 = INQUIRYID
GO
ALTER TABLE MyTable
DROP COLUMN INQUIRYID

But this a bad idea. What about the existing database object that reference the existing column. Oh well too late. A thousand application buga have just been created. Update your resume. You want biggie size|||if you can connect the db thru EM, just type the new name....|||if you can connect the db thru EM, just type the new name....

The Holy Book says ...

sp_rename

Changes the name of a user-created object (for example, table, column, or user-defineddata type) in the current database.
Syntax

sp_rename [ @.objname = ] 'object_name' ,
[ @.newname = ] 'new_name'
[ , [ @.objtype = ] 'object_type' ]
Arguments

[@.objname =] 'object_name'
Is the current name of the user object (table, view, column, stored procedure, trigger, default, database, object, or rule) or data type. If the object to be renamed is a column in a table, object_name must be in the form table.column. If the object to be renamed is an index, object_name must be in the form table.index. object_name is nvarchar(776), with no default.
[@.newname =] 'new_name'
Is the new name for the specified object. new_name must be a one-part name and must follow the rules for identifiers. newname is sysname, with no default.
[@.objtype =] 'object_type'
Is the type of object being renamed. object_type is varchar(13), with a default of NULL, and can be one of these values.
ValueDescriptionCOLUMNA column to be renamed.DATABASEA user-defined database. This option is required when renaming a database.INDEXA user-defined index.OBJECTAn item of a type tracked in sysobjects. For example, OBJECT could be used to rename objects including constraints (CHECK, FOREIGN KEY, PRIMARY/UNIQUE KEY), user tables, views, stored procedures, triggers, and rules.USERDATATYPEA user-defined data type added by executing sp_addtype.


I don't know why you are providing a round about way of doing the same thing.|||You can try out this one.

EXECUTE sp_rename N'MyTable.MyCurrFName', N'MyNewFName', 'COLUMN'

MyTable has a Field MyCurrFName. It will be renamed to MyNewFName|||You are better off either a). Create a view that has the name you want, or b). unloading, dropping, create, and load

There was a very good article that discusses why do the alter causes sql server to waste a lot of space on the data pages...Now if I can find on Nigel's web site I'll post a link|||Here's the link

http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html|||thanks Brett, thats really a good link. never thought of it.

Changing field length changes AllowNulls=True

(If this is in the wrong newsgroup, let me know and I'll move it.)
Using SQLDMO from a VB app to modify an existing field in a SQL 2000
database.
All the fields in my database have AllowNulls = False.
After changing a field size in the manner below, I look at the table in
Enterprise Manager and Allow Nulls is now True! This is fully repeatable.
Here's the code:
Dim oTable As sqldmo.Table
Dim oColumn As New sqldmo.Column
'skip the Set statements..
oTable.BeginAlter
oColumn.Length = pSize
oTable.DoAlter
I do not believe the version of SQLDMO matters as this happens on older as
well as newer versions.
Any ideas?
Thanks,
Jerry
I have not used DMO objects much, but in scripting when altering a field
size, if you don't specify not null in the alter it defaults to null. So in
your DMO could maybe set the field to not null before updating it.
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Jerry J" wrote:

> (If this is in the wrong newsgroup, let me know and I'll move it.)
> Using SQLDMO from a VB app to modify an existing field in a SQL 2000
> database.
> All the fields in my database have AllowNulls = False.
> After changing a field size in the manner below, I look at the table in
> Enterprise Manager and Allow Nulls is now True! This is fully repeatable.
> Here's the code:
> Dim oTable As sqldmo.Table
> Dim oColumn As New sqldmo.Column
> 'skip the Set statements..
> oTable.BeginAlter
> oColumn.Length = pSize
> oTable.DoAlter
> I do not believe the version of SQLDMO matters as this happens on older as
> well as newer versions.
> Any ideas?
> Thanks,
> Jerry
>
>

Changing field length changes AllowNulls=True

(If this is in the wrong newsgroup, let me know and I'll move it.)
Using SQLDMO from a VB app to modify an existing field in a SQL 2000
database.
All the fields in my database have AllowNulls = False.
After changing a field size in the manner below, I look at the table in
Enterprise Manager and Allow Nulls is now True! This is fully repeatable.
Here's the code:
Dim oTable As sqldmo.Table
Dim oColumn As New sqldmo.Column
'skip the Set statements..
oTable.BeginAlter
oColumn.Length = pSize
oTable.DoAlter
I do not believe the version of SQLDMO matters as this happens on older as
well as newer versions.
Any ideas?
Thanks,
JerryI have not used DMO objects much, but in scripting when altering a field
size, if you don't specify not null in the alter it defaults to null. So in
your DMO could maybe set the field to not null before updating it.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Jerry J" wrote:
> (If this is in the wrong newsgroup, let me know and I'll move it.)
> Using SQLDMO from a VB app to modify an existing field in a SQL 2000
> database.
> All the fields in my database have AllowNulls = False.
> After changing a field size in the manner below, I look at the table in
> Enterprise Manager and Allow Nulls is now True! This is fully repeatable.
> Here's the code:
> Dim oTable As sqldmo.Table
> Dim oColumn As New sqldmo.Column
> 'skip the Set statements..
> oTable.BeginAlter
> oColumn.Length = pSize
> oTable.DoAlter
> I do not believe the version of SQLDMO matters as this happens on older as
> well as newer versions.
> Any ideas?
> Thanks,
> Jerry
>
>

Changing field length changes AllowNulls=True

(If this is in the wrong newsgroup, let me know and I'll move it.)
Using SQLDMO from a VB app to modify an existing field in a SQL 2000
database.
All the fields in my database have AllowNulls = False.
After changing a field size in the manner below, I look at the table in
Enterprise Manager and Allow Nulls is now True! This is fully repeatable.
Here's the code:
Dim oTable As sqldmo.Table
Dim oColumn As New sqldmo.Column
'skip the Set statements..
oTable.BeginAlter
oColumn.Length = pSize
oTable.DoAlter
I do not believe the version of SQLDMO matters as this happens on older as
well as newer versions.
Any ideas?
Thanks,
JerryI have not used DMO objects much, but in scripting when altering a field
size, if you don't specify not null in the alter it defaults to null. So in
your DMO could maybe set the field to not null before updating it.
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Jerry J" wrote:

> (If this is in the wrong newsgroup, let me know and I'll move it.)
> Using SQLDMO from a VB app to modify an existing field in a SQL 2000
> database.
> All the fields in my database have AllowNulls = False.
> After changing a field size in the manner below, I look at the table in
> Enterprise Manager and Allow Nulls is now True! This is fully repeatable.
> Here's the code:
> Dim oTable As sqldmo.Table
> Dim oColumn As New sqldmo.Column
> 'skip the Set statements..
> oTable.BeginAlter
> oColumn.Length = pSize
> oTable.DoAlter
> I do not believe the version of SQLDMO matters as this happens on older as
> well as newer versions.
> Any ideas?
> Thanks,
> Jerry
>
>

changing existind dimension datasource

Anybody knows how to change a the datasource for an existing Dimension?

I've tryed deleting them but since i've imported the cube from a diferent server, if I try to delete the dimension it is also deleted from the cubes.

thanks for the help in advance.

Hugo

Not sure if your question is for AS2000 or AS2005.

If it is AS2000, you can change the datasource connetion string. That means it will affect other objects that pointing to this datasource.

If it is AS2005, you should add a table from different datasource and create cube and dimension as usual. Steps are:

1) Create a primary datasource (where you cube is) and dsv

2) Create a secondary datasource (where your dimension is)

3) In dsv designer, select Add\Remove tables. You see the dialog which has darasource drop down box. Select the secondary datasource and add the dimension table to dsv.

4) You may want to create relationship between fact table and dimension table before you create cube and dimension. After that, just now cube wizard as usual.

Hope this helps

Ken

|||

ok. thanks Ken for the info.

My SQL is a 2k.- Thanks for your quick response. I've tried it and it works.

Changing default value for a column

I have an existing database and I want to change it so that if no value is
inserted into a column that it populates that column with a zero instead of
a
null value. How do I make this change? I suspect I need to change all the
existing nulls to zero before making the change? Any help would be greatly
appreciated.That depends. Are you happy with the existing nulls? If so, simply add a
default constraint:
alter table MyTable
add
constraint DF1_MyTable default (0) for MyColumn
However, if you need to keep all nulls out, then populate the null columns
with some value and then alter the column:
alter table MyTable
alter column
MyColumn int not null
go
Then, add the default constraint as shown above.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:722BC848-15EA-474D-8F40-6BB078F7CDF3@.microsoft.com...
I have an existing database and I want to change it so that if no value is
inserted into a column that it populates that column with a zero instead of
a
null value. How do I make this change? I suspect I need to change all the
existing nulls to zero before making the change? Any help would be greatly
appreciated.

Changing default value for a column

I have an existing database and I want to change it so that if no value is
inserted into a column that it populates that column with a zero instead of a
null value. How do I make this change? I suspect I need to change all the
existing nulls to zero before making the change? Any help would be greatly
appreciated.
That depends. Are you happy with the existing nulls? If so, simply add a
default constraint:
alter table MyTable
add
constraint DF1_MyTable default (0) for MyColumn
However, if you need to keep all nulls out, then populate the null columns
with some value and then alter the column:
alter table MyTable
alter column
MyColumn int not null
go
Then, add the default constraint as shown above.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:722BC848-15EA-474D-8F40-6BB078F7CDF3@.microsoft.com...
I have an existing database and I want to change it so that if no value is
inserted into a column that it populates that column with a zero instead of
a
null value. How do I make this change? I suspect I need to change all the
existing nulls to zero before making the change? Any help would be greatly
appreciated.

Changing default value for a column

I have an existing database and I want to change it so that if no value is
inserted into a column that it populates that column with a zero instead of a
null value. How do I make this change? I suspect I need to change all the
existing nulls to zero before making the change? Any help would be greatly
appreciated.That depends. Are you happy with the existing nulls? If so, simply add a
default constraint:
alter table MyTable
add
constraint DF1_MyTable default (0) for MyColumn
However, if you need to keep all nulls out, then populate the null columns
with some value and then alter the column:
alter table MyTable
alter column
MyColumn int not null
go
Then, add the default constraint as shown above.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:722BC848-15EA-474D-8F40-6BB078F7CDF3@.microsoft.com...
I have an existing database and I want to change it so that if no value is
inserted into a column that it populates that column with a zero instead of
a
null value. How do I make this change? I suspect I need to change all the
existing nulls to zero before making the change? Any help would be greatly
appreciated.

Changing data type

Use databases a bit, but new to SQL Server. We just want to change a column of existing SQL Server 2005 data from a string data type to one of the UNiCODE data types, such as DT_WSTR or DT.NTEXT (such as one can use for various data mining tasks, etc.). It seems to do this one needs to "the data conversion transformation editor". To use that one has to have a package and a project?

Does any one have a full script or set of steps to do the full set of steps for what should be a simple task? This would be a great example for BOL, but each atomistic bit of BOL refers to another, and one gets lost in the circle when a complete example is needed for fundamantal housekeeping tasks.

Yes you need a project and a package. To get started with SSIS projects and packages, you should run through the SSIS tutorial. See http://msdn2.microsoft.com/en-us/library/ms170419(SQL.90).aspx - the steps of the tutorial take you through the project and package creation and into working with data flow

When you need to convert data, this BOL entry gives the steps for using the Data Conversion Component. http://msdn2.microsoft.com/en-us/library/ms140321.aspx

Donald

|||

Fairly new to SQL Server 2005, so please excuse a more basic question. I very much appreciate any further hint or clarification that you can provide!

It is sometime at least appears rather unclear to quickly see the necessary "big picture" in SQL 2005! For example, when is it best to use "graphical tools" (with projects and packages), or, can one use simple Transact- SQL statements to perhaps best perform the same (relatively simple) operation? As here, for example, to change column data type, could I not use the SQL commands ALTER TABLE, and/or, say CAST and CONVERT? Do these transforms work into the Unicode data types, such as DT_WSTR?

I very much appreciate any further hint or thought!

|||

J. Lewis wrote:

Fairly new to SQL Server 2005, so please excuse a more basic question. I very much appreciate any further hint or clarification that you can provide!

It is sometime at least appears rather unclear to quickly see the necessary "big picture" in SQL 2005! For example, when is it best to use "graphical tools" (with projects and packages), or, can one use simple Transact- SQL statements to perhaps best perform the same (relatively simple) operation? As here, for example, to change column data type, could I not use the SQL commands ALTER TABLE, and/or, say CAST and CONVERT? Do these transforms work into the Unicode data types, such as DT_WSTR?

I very much appreciate any further hint or thought!

J,

There appears to be some confusion between SQL Server Database Engine and SQL Server Integration Services.

Tables are stored in SQL Server database engine and can be manipulated using ALTER TABLE.

CAST and CONVERT are T-SQL fuctions. T-SQL is a programming language used to manipulate the DATA that is stored in tables (note the distinction here between ALTER TABLE which only operates on the table itself).

DT_WSTR is a data type within SQL Server Integration Services. It is NOT a data type within SQL Server Database Engine. Hence, CAST and CONVERT will not work on columns of type DT_WSTR.

With all that in mind, can you explain again exactly what it is you require to be able to do?

-Jamie

|||

Thank you greatly -- your explanation is really clear and very helpful. One does not always see the "big picture", when just looking at individual BOL pages!

What trying to do is is set-up to use the Term Extraction Transformation which as we understand it requires use of the DT_WSTR or DT_NTEXT data types. This is a fairly limited, focused job we were trying to complete in SQL Server 200 5. It had sadly, frankly not fully hit us that there were different data types across various components of SQL Server.

As we have a bit "in /out" job to do here, we are now at least hoping to find a simple, but reasonably complete, example script to set up a project/package to read in an input file, convert a data type in a column, and then run a Term Extraction.

Thank you for your help.

|||

Term Extraction Transform is part of SQL Server Integration Services so you are in the right place.

It sounds like you are a beginner so I would recommend you first watch this webcast: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032289998&EventCategory=5&culture=en-US&CountryCode=US and this: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032273477&EventCategory=5&culture=en-US&CountryCode=US to introduce yourself to the product.

As for term extraction, I haven't seen much material although I vaguely recall a webcast that Donald Farmer (further up this thread) did in which it was mentioned. I can't find that webcast though. Hopefully Donald will reply and let you know.

-Jamie

Changing Data Source of Existing Cube

Hi Guys,

We had existing cubes in our Analysis Server, we were required to move them on another Reporting Server which would be using Data Replicated every night to that server. Problem is now source data is divided into 2 Reporting Database Servers. Table Names/View Names are the same in all the Databases. I just want to change the data source pointing to existing Database to the new Reporting Server. Can you tell me how this can be achieved?

Regards,

Kaushal

I have the same question while i also can't find better ways

in fact I have to create cube for another time to change datasource

|||

Looks like RS-related question.

Moving to reporting services forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||By Reporting Server, do you mean SSRS? It looks like you just need to change the connection string inside the SSAS database.