Altering the identity seed of a table

Hi,

Im trying to alter the identity seed of a table in a script and I cant work out how to do so without doing it the way Enterprise Manager does it - ie create a tmp table with the new id, populate it with data and set constraints etc, then drop the original table and rename the tmp one.

This is pretty hard to script for arbitrary tables automatically, so I was wondering if there is some way to do it with an ALTER TABLE script?

cheers
Pete StoreyDBCC Checkident.|||Thanks!

Altering column names?

I have been looking in the SQL Server Help Files, but I cant seem to find the syntax to change the name of a column name..?
Can it be done? if so, what is the syntax?well, i guess you could do this:alter table foo
add column barnew datatype etc.

update table foo
set barnew = bar

update table foo
drop column bar

but why? i mean, you can change the name in any select statement:select bar as barnew
from foo|||I want to be able to change the names of columns because I am building an interface to an SQL Database.

That code you gave me doesnt work, you dont use the word 'column' when adding a new column, you just use 'add' alone.|||sorry, i do not always test out the syntax i suggest

but at least i got across the idea of the approach to use

you're welcome|||Well .. i think sp_rename works on columnt too ... need to check that out ..

create table myTab (mycol int)
go
sp_rename 'mytab.mycol' ,'mytab.mycol1'
go
select mycol1 from myTab
go

Seems to worksql

Alter table impossible due to replication

Hi !
I can't alter a table because of an old problematical replication.
I've already tried to clean the replication informations with
sp_removedbreplication and sp_msunmarkreplinfo. I've already update
sysobjects to set replinfo to 0 for the table that I try to update. I've
also the database from the replication configuration.
When I try to alter the table, I receive this message in french :
table 'Espaces'
- Impossible de modifier la table.
Erreur ODBC : [Microsoft][ODBC SQL Server Driver][SQL Server]chec de ALTER
TABLE DROP COLUMN car 'Esp_Ceremonie' est actuellement rpliqu.
In english, I think it would be : Error ALTER TABLE DROP COLUMN because
'xxxx' is at the moment replicated.
I hope someone can help me !
Thanks in advance !!!!
Bernard
bernard.borsu@.odysseos.net
can you try this:
EXEC sp_configure 'allow',1
go
reconfigure with override
go
use your_database_name
go
update sysobjects set replinfo = 0 where name = 'your_table_name'
go
EXEC sp_configure 'allow',0
go
reconfigure with override
go
(thanks to Vyas http://vyaskn.tripod.com/repl_ans3.htm#replinfo for this)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Bernard Borsu" <bernard.borsu@.odysseos.net> wrote in message
news:ulGiJaA%23EHA.2680@.TK2MSFTNGP09.phx.gbl...
> Hi !
> I can't alter a table because of an old problematical replication.
> I've already tried to clean the replication informations with
> sp_removedbreplication and sp_msunmarkreplinfo. I've already update
> sysobjects to set replinfo to 0 for the table that I try to update. I've
> also the database from the replication configuration.
> When I try to alter the table, I receive this message in french :
> table 'Espaces'
> - Impossible de modifier la table.
> Erreur ODBC : [Microsoft][ODBC SQL Server Driver][SQL Server]chec de
ALTER
> TABLE DROP COLUMN car 'Esp_Ceremonie' est actuellement rpliqu.
> In english, I think it would be : Error ALTER TABLE DROP COLUMN because
> 'xxxx' is at the moment replicated.
> I hope someone can help me !
> Thanks in advance !!!!
> --
> Bernard
> bernard.borsu@.odysseos.net
>
|||Thanks for your solution, but i've already tried this solution and the error
message is always the same.
"Hilary Cotter" <hilary.cotter@.gmail.com> a crit dans le message de news:
Odw1pwB%23EHA.1296@.TK2MSFTNGP10.phx.gbl...
> can you try this:
> EXEC sp_configure 'allow',1
> go
> reconfigure with override
> go
> use your_database_name
> go
> update sysobjects set replinfo = 0 where name = 'your_table_name'
> go
> EXEC sp_configure 'allow',0
> go
> reconfigure with override
> go
> (thanks to Vyas http://vyaskn.tripod.com/repl_ans3.htm#replinfo for this)
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Bernard Borsu" <bernard.borsu@.odysseos.net> wrote in message
> news:ulGiJaA%23EHA.2680@.TK2MSFTNGP09.phx.gbl...
> ALTER
>

Alter table

I know I can't do an alter table on a replicated table because it is
being replicated. But, in my software, if I need to modify a table I do
an alter table and add the new column.
What is the easy way, via SQL script, to see if this machine is a
distributor/publisher for replication, in which case I need to do the
sp_repladdcolumn, or a subscriber, in which case I need to do nothing
because the dist/pub will do it, or neither, in which case I need to do
the alter table?
Thanks.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Darin,
I'd probably use something like this:
declare @.mytablename varchar(100)
set @.mytablename = 'testtr'
if exists(SELECT name FROM sysarticles where name =
@.mytablename)
or exists(SELECT name FROM sysarticles where name =
@.mytablename)
select 'exists'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Did you mean for both of the select statements to be the same?
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Sorry - second one is for merge...
Actually it lacked a bit more code which I've added. There are 2 versions
and the second one would be more elegant (you'll need to test it).
Cheers,
Paul Ibison
declare @.mytablename varchar(100)
set @.mytablename = 'testtr'
if (select object_id('sysarticles')) is not null
begin
if exists (SELECT name FROM sysarticles where name =
@.mytablename)
select 'yes'
end
if (select object_id('sysmergearticles')) is not null
begin
if exists(SELECT name FROM sysmergearticles where name =
@.mytablename)
select 'yes'
end
if (select replinfo from sysobjects where name = @.mytablename) > 0
select 'yes'

Alter Database Move column

Hi,
Is it possible to move a column using an SQL script?
I have looked at the Alter Table syntax help but can't see any indication of
ordinal control .
I have a table that I have had to add an indentity column to.
I want to move it to pos 0.
My script so far is :
alter table MasterStationTest
drop constraint pk_masterstationtest
go
alter table MasterStationTest
add id int
IDENTITY(1,1)
PRIMARY KEY
go
thanks
Bob
No, you have to drop and re-create the table for that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob Clegg" <bclegg@.clear.net.nz> wrote in message news:%23cIRcW9PEHA.3304@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to move a column using an SQL script?
> I have looked at the Alter Table syntax help but can't see any indication of
> ordinal control .
> I have a table that I have had to add an indentity column to.
> I want to move it to pos 0.
> My script so far is :
> alter table MasterStationTest
> drop constraint pk_masterstationtest
> go
> alter table MasterStationTest
> add id int
> IDENTITY(1,1)
> PRIMARY KEY
> go
> thanks
> Bob
>
|||Hi,
No , You cant change the column position using ALTER table statement. The
only solution is:-
1. Create a new table with new structure with identity property
2. Insert data into the new table from actual table (Execlude the col1 which
is identiy)
insert into new_table(col2,col3...coln) select col1,col2,col3...coln from
actual_table
3. Generate Script for indexes and dependant objects
4. Verify all the data are moved successfully
5. Drop the actual table
6. Rename the new_table to actual table using (sp_rename new_table,
actual_table)
7. Create the index in the table (execute the script generated in step-3)
Note:
Enterprise manager will do the above steps when you change column postions
or add columns .....
Thanks
Hari
MCDBA
"Bob Clegg" <bclegg@.clear.net.nz> wrote in message
news:#cIRcW9PEHA.3304@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to move a column using an SQL script?
> I have looked at the Alter Table syntax help but can't see any indication
of
> ordinal control .
> I have a table that I have had to add an indentity column to.
> I want to move it to pos 0.
> My script so far is :
> alter table MasterStationTest
> drop constraint pk_masterstationtest
> go
> alter table MasterStationTest
> add id int
> IDENTITY(1,1)
> PRIMARY KEY
> go
> thanks
> Bob
>
|||Although you didn't ask, Bob, it is often considered to be a flaw when we
depend on the physical ordering of the columns in a table... When I first
started doing this stuff many years ago, I tried to keep columns in tables
in a particular order, and found myself dropping/recreating tables all of
the time ( all on nights and weekends as well).
I encouraged the programmers to always use a column list and life got
better...
I'm not trying to tell you how to do your job, just making an observation
you might find helpful...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob Clegg" <bclegg@.clear.net.nz> wrote in message
news:%23cIRcW9PEHA.3304@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to move a column using an SQL script?
> I have looked at the Alter Table syntax help but can't see any indication
of
> ordinal control .
> I have a table that I have had to add an indentity column to.
> I want to move it to pos 0.
> My script so far is :
> alter table MasterStationTest
> drop constraint pk_masterstationtest
> go
> alter table MasterStationTest
> add id int
> IDENTITY(1,1)
> PRIMARY KEY
> go
> thanks
> Bob
>

Alt+X doesent work! argghhh..

must have done something magical in QA, cant run my statements..going mad...when pressing f5 or alt+x i get this f****ng save results box..have tried everything, not even the so trustworthy restart helped..
help!?What about [CTRL]+E...

It's got to be a setting, though I can't find it...

Are you on a client?

Maybe a reinstall....|||Check in the options under the results tab. Make sure you are not funneling output to a file.|||Press Ctrl+D & then use Alt+X

Allow Blank in parm not working

I am using RS2005. I have a data driven parm that I have checked "allow blank" as a value. I cant allow null so that isnt checked. I have even entered a space in the area where you can provide a default value. When I preview the report ... this parameter has <Select a Value> in the combo box. All other parms are filled in with defaults so the report should run. I hit "View Report" and get an error message "Please select a value for the parameter 'ParameterX' " which is the parameter I have said could be blank. Of course if I drop down the list there isnt a blank value to select - and I dont see why I would want to add one and force a selection of spaces ... seems like something is wrong here. Please help if you can. I am hoping it is just something simple.

Hello,

So let me get this right you have a drop down in the report that you want to be able to display a blank value you and contain a black value at the same time ?

what you can do is add a few lines of code to the sproc or sql statement that produces the resaults for the drop down parameter .

~~~~~~~~~~~ sample code

select EmployeeName, 2 as sort_col

from empdb

union all

select ' ' as EmployeeName, 1 as sort_col

Order by sort_col

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This will work in TSQL and there is a work around for oracle and teradata

|||

Thanks Charles,

I am using RS2005. The parm is a drop down list box. My question is why does my user have to select *anything* for that parameter if they want to leave it blank? Why do I have to insert a join to my query to add in a blank value which appears as a line of spaces in the drop down list. If the list is left untouched, and I have provided a default value of spaces (when I set up the parameter I gave it a default of spaces) - then why would it not work? It shows <Select a Value> text in the window of the list and forces you to make a selection ... if you want to leave it blank ... that is not an option. What is the purpose of having a check box "allow blanks" when you set up the parameter if there isnt any way to exercise the option of leaving it blank? I dont want to make my user *select* a blank line ... they should just be able to bypass that parameter if they dont want to make a selection.

|||

The allow blank works for input parameters. in order for blanks to be aloud in a drop down the sproc behind it must supplied the blank value

|||it seems very strange to me that I have to make them *choose* a blank ... since the idea is to minimize keystrokes by supplying a default value. Thanks for the reply :-)

all of sudden cant connect with SQL server authentication

Can connect with Windows NT authentication, but not with SQL server auth. So
all my
code has stopped working ...
When I try to make a new SQL Server registration
I get server does not exist or access denied...Barrett Bonden wrote:
> Can connect with Windows NT authentication, but not with SQL server auth.
So
> all my
> code has stopped working ...
> When I try to make a new SQL Server registration
> I get server does not exist or access denied...
>
Make sure your SQL Server is configured for mixed mode Authentication.
It sounds like it is only configured for Windows Authentication. If you
have sysadmin authority just fire up Enterprise Manager, right-click
your server, and then click Properties. On the Security tab, under
Authentication, click Mixed Mode.
If you are not a sysadmin, you have to update the Registry directly. I
believe the correct key will be called:
HKLM\Software\Microsoft\MSSqlserver\MSSq
lServer\LoginMode
change the DWORD value to 2
If you have a named instance installed the Registry path will be
something like:
HKEY_LOCAL_MACHINE\Software\Microsoft\Mi
crosoft SQL Server\Instance
Name\MSSQLServer\LoginMode
Good luck ...|||Did somebody switched the authentication mode to "Windows Auth" only ?
The mixed Authentication allows Windows Auth. as well as SQL Server
Authentication. try to log on with an adminstrative account on the SQL
Server and set the Authentication mode back to mixed authentication
then.
HTH, Jens Suessmeyer.

all of sudden cant connect with SQL server authentication

Can connect with Windows NT authentication, but not with SQL server auth. So
all my
code has stopped working ...
When I try to make a new SQL Server registration
I get server does not exist or access denied...
Barrett Bonden wrote:
> Can connect with Windows NT authentication, but not with SQL server auth. So
> all my
> code has stopped working ...
> When I try to make a new SQL Server registration
> I get server does not exist or access denied...
>
Make sure your SQL Server is configured for mixed mode Authentication.
It sounds like it is only configured for Windows Authentication. If you
have sysadmin authority just fire up Enterprise Manager, right-click
your server, and then click Properties. On the Security tab, under
Authentication, click Mixed Mode.
If you are not a sysadmin, you have to update the Registry directly. I
believe the correct key will be called:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\Lo ginMode
change the DWORD value to 2
If you have a named instance installed the Registry path will be
something like:
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance
Name\MSSQLServer\LoginMode
Good luck ...
|||Did somebody switched the authentication mode to "Windows Auth" only ?
The mixed Authentication allows Windows Auth. as well as SQL Server
Authentication. try to log on with an adminstrative account on the SQL
Server and set the Authentication mode back to mixed authentication
then.
HTH, Jens Suessmeyer.

all of a sudden, can't connect

I have SQL Server 2000, not sure which SP. A db that I created 3+ years ago
has been running smoothly, but today, I cannot connect. Specifically, in
Enterprise mgr, when I click on the name in the tree on the left, or if I
right-click and select Connect, I get a message box which says:
"A connection could not be established to (nodename). Reason: cannot open
user default database. Login failed. Please verify SQL Server is running and
check your SQL Server registration properties by right-clicking the node and
try again."
So I verified that the SQL Server services were running in the Servies
dialog. I even restarted them. I right-clicked the node in Enterprise mgr,
and it won't show my anything in Properties. But when I selected "Edit
Registration Properties", it was a simple dialog with not much on it. I use
Windows authentication (this is a development server, and only I have access
to it), and the only other option is to change the server group, of which
there is only one.
I tried deleting the registration for this server, then re-adding, it, but
it wouldn't let me re-add. I should note that this was done from another
machine, as I didn't want to try deleting the node from the console on the
actual machine where this DB resides. It should be noted that I was getting
this error on both machines.
One more note: In order to copy this database over to another machine which
is running SQL Server 2005 Express, I detached the database a few days ago,
copied it over, and attached it. I thought I had re-attached it in this 2000
machine, but whether I did or didn't, I don't see a way to do it now.
I am at a loss. Any advice?
I should add one more thing. My ASP app which points to that database works
fine, and I have verified that it is pulling data from it. So my problem is
not rooted in failing to re-attach the database. I just cannot connect via
Enterprise manager.
|||> "A connection could not be established to (nodename). Reason: cannot open
> user default database. Login failed. Please verify SQL Server is running
> and check your SQL Server registration properties by right-clicking the
> node and try again."
Read the error message VERY carefully. Note the information about the
default database. It apparently no longer exists. Note that this really
isn't an error - only EM treats it as one. You can still access the server
using QA using the same credentials, where you can change the default
database (sp_defaultdb) to something that is valid. Alternatively, you can
change the EM registration to a login that works (or login as an
administrator with access to the server - if using integrated security) and
change the default database.
|||I can't make it work. Your post says that I can still get in via EM, but I
cannot. It gives me the same error. So I logged in using not my network ID
(because I'm using Windows authentication), but the web user's ID that I use
in my ASP connection string. And it let me in, but it wouldn't let me run
this query:
I tried running EXEC sp_defaultdb '*username*', '*dbname*'
because for username, I put in my Windows network login, and it says you
cannot change someone else's default database.
I have no idea what administrator name I have for this thing. It's been so
long, and I don't recall it asking me that question when I first created
this thing on this server.
|||>I can't make it work. Your post says that I can still get in via EM, but I
>cannot. It gives me the same error. So I logged in using not my network ID
Read the information I posted again. You will need to change the
registration in EM to use a different identity if you want to use EM to
change the default database. Otherwise, use isql (note - QA has the same
problem; I was wrong about that).

> (because I'm using Windows authentication), but the web user's ID that I
> use in my ASP connection string. And it let me in, but it wouldn't let me
> run this query:
> I tried running EXEC sp_defaultdb '*username*', '*dbname*'
> because for username, I put in my Windows network login, and it says you
> cannot change someone else's default database.
Yes - just what I would expect. Users shouldn't be able to change security
information, especially about other users.

> I have no idea what administrator name I have for this thing. It's been so
> long, and I don't recall it asking me that question when I first created
> this thing on this server.
Not much I can do about that. When you set up a server (or a database), you
should be documenting what options you choose just in case something "bad"
happens. If you are using mixed-mode security, you should be able to access
the server using the sa (or any other administrator-level) login (assuming
you still remember the password). Otherwise, you need to login to the
client with an administrator-level login to make these changes.
|||I was able to get in and make the change using sa. Thanks

all of a sudden, can't connect

I have SQL Server 2000, not sure which SP. A db that I created 3+ years ago
has been running smoothly, but today, I cannot connect. Specifically, in
Enterprise mgr, when I click on the name in the tree on the left, or if I
right-click and select Connect, I get a message box which says:
"A connection could not be established to (nodename). Reason: cannot open
user default database. Login failed. Please verify SQL Server is running and
check your SQL Server registration properties by right-clicking the node and
try again."
So I verified that the SQL Server services were running in the Servies
dialog. I even restarted them. I right-clicked the node in Enterprise mgr,
and it won't show my anything in Properties. But when I selected "Edit
Registration Properties", it was a simple dialog with not much on it. I use
Windows authentication (this is a development server, and only I have access
to it), and the only other option is to change the server group, of which
there is only one.
I tried deleting the registration for this server, then re-adding, it, but
it wouldn't let me re-add. I should note that this was done from another
machine, as I didn't want to try deleting the node from the console on the
actual machine where this DB resides. It should be noted that I was getting
this error on both machines.
One more note: In order to copy this database over to another machine which
is running SQL Server 2005 Express, I detached the database a few days ago,
copied it over, and attached it. I thought I had re-attached it in this 2000
machine, but whether I did or didn't, I don't see a way to do it now.
I am at a loss. Any advice?I should add one more thing. My ASP app which points to that database works
fine, and I have verified that it is pulling data from it. So my problem is
not rooted in failing to re-attach the database. I just cannot connect via
Enterprise manager.|||> "A connection could not be established to (nodename). Reason: cannot open
> user default database. Login failed. Please verify SQL Server is running
> and check your SQL Server registration properties by right-clicking the
> node and try again."
Read the error message VERY carefully. Note the information about the
default database. It apparently no longer exists. Note that this really
isn't an error - only EM treats it as one. You can still access the server
using QA using the same credentials, where you can change the default
database (sp_defaultdb) to something that is valid. Alternatively, you can
change the EM registration to a login that works (or login as an
administrator with access to the server - if using integrated security) and
change the default database.|||I can't make it work. Your post says that I can still get in via EM, but I
cannot. It gives me the same error. So I logged in using not my network ID
(because I'm using Windows authentication), but the web user's ID that I use
in my ASP connection string. And it let me in, but it wouldn't let me run
this query:
I tried running EXEC sp_defaultdb '*username*', '*dbname*'
because for username, I put in my Windows network login, and it says you
cannot change someone else's default database.
I have no idea what administrator name I have for this thing. It's been so
long, and I don't recall it asking me that question when I first created
this thing on this server.|||>I can't make it work. Your post says that I can still get in via EM, but I
>cannot. It gives me the same error. So I logged in using not my network ID
Read the information I posted again. You will need to change the
registration in EM to use a different identity if you want to use EM to
change the default database. Otherwise, use isql (note - QA has the same
problem; I was wrong about that).

> (because I'm using Windows authentication), but the web user's ID that I
> use in my ASP connection string. And it let me in, but it wouldn't let me
> run this query:
> I tried running EXEC sp_defaultdb '*username*', '*dbname*'
> because for username, I put in my Windows network login, and it says you
> cannot change someone else's default database.
Yes - just what I would expect. Users shouldn't be able to change security
information, especially about other users.

> I have no idea what administrator name I have for this thing. It's been so
> long, and I don't recall it asking me that question when I first created
> this thing on this server.
Not much I can do about that. When you set up a server (or a database), you
should be documenting what options you choose just in case something "bad"
happens. If you are using mixed-mode security, you should be able to access
the server using the sa (or any other administrator-level) login (assuming
you still remember the password). Otherwise, you need to login to the
client with an administrator-level login to make these changes.|||I was able to get in and make the change using sa. Thanks

All of a sudden i Cant Create Stored Procedures?

When creating even the simplest of Stored Procedures (that i know work!), i get prompted with an error Titled : <Microsoft SQL-DMO (ODBC SQLSTATE: 42000)> with an error message: <Error 170: Line 1: Incorrect syntax near "My Stored Proc Name". Must declare the variable '@.str_PartNo'>

I know for a fact that the stored procedure does not have a syntax error because i can run a stored proc that is already saved, but if i copy and paste it into a new store proc, i get this error! Also, when i click the check syntax there are no errors.

I was told to verify if the service <SQL Server Agent> is running. It is.

I could really use some helpThe agent won't affect this
Suspect either you aren't copying the correct data or there is an invalid character somewhere.

First thing to do is look for the definition of @.str_PartNo and see why it is not defined.
The message is saying it has a problem at line 1 which is a bit odd - do you have an exec of dynamic sql after a go somewhere?

try
create proc myproc
as
select 1
go

The copy and paste may be introducing errors due to invalid characters.|||it works, it seems the problem was that i was encompassing my Stored Proc name with double quotes, i've saved others with the double quotes...strange.

Thanks for the help|||It's because of the settings in the environment you are creating the sp from.
look at

set QUOTED_IDENTIFIER off
go
create procedure "mysp"
as
select 1
go

set QUOTED_IDENTIFIER on
go
create procedure "mysp"
as
select 1
go

drop procedure mysp