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!

Alter Table question

(using SQL Server 2000)
I notice that in the enterprise manager, I can insert a column into an
existing table at any position that I want (so, if my table has 3 columns,
and I want to add a fourth, I can put the column at the end, but I could
also insert it between the 1st and second columns).
Is there a way to do that with an SQL Alter Table statement (control
position of the new column)?No. If you script the code that EM uses you will see that it actually
creates a new table from scratch and then populates it with the old data.
--
David Portas
--
Please reply only to the newsgroup
--
"J.Marsch" <jeremy@.ctcdeveloper.com> wrote in message
news:OhX4r3TqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> (using SQL Server 2000)
> I notice that in the enterprise manager, I can insert a column into an
> existing table at any position that I want (so, if my table has 3 columns,
> and I want to add a fourth, I can put the column at the end, but I could
> also insert it between the 1st and second columns).
> Is there a way to do that with an SQL Alter Table statement (control
> position of the new column)?
>
>|||Wow. That response was just about instantaneous. Thank you!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:KNSdnc3thOvL-i-iRVn-tA@.giganews.com...
> No. If you script the code that EM uses you will see that it actually
> creates a new table from scratch and then populates it with the old data.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
> "J.Marsch" <jeremy@.ctcdeveloper.com> wrote in message
> news:OhX4r3TqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> > (using SQL Server 2000)
> > I notice that in the enterprise manager, I can insert a column into an
> > existing table at any position that I want (so, if my table has 3
columns,
> > and I want to add a fourth, I can put the column at the end, but I could
> > also insert it between the 1st and second columns).
> >
> > Is there a way to do that with an SQL Alter Table statement (control
> > position of the new column)?
> >
> >
> >
>

ALTER table option is not available

Does anyone know why the ALTER to option is not available on Query Analyzer, Enterprise Manager or MS SQL Server Management Studio?

On Query Analyzer and Enterprise Manager, this option is visible by right clicking on the table, selecting "Script object to New Window" and "Alter".

On MS SQL Server Management Studio, this option is visible by right clicking on the table, selecting "Script table as", then "Alter to" is visible but not available. I'm logged in under the db_owner role.

Any ideas?

Hi saleyoum,

This functionality should be the same in all versions of the tools you mentioned above (i.e. Query Analyzer, EM, SSMS)...meaning, you shouldn't be able to generate an alter table script from any of the GUI's...is that what you are seeing, or are you saying you are seeing it as possible in Query Analyzer and Enterprise Manager (you shouldn't be I hope :-))...

Basically, there are so many possibilities with altering a table, it would be near impossible to generate an alter script template to a new window/clipboard/etc....you could want to alter a column, multiple columns, all columns, add columns, drop columns, manage constraints (table and column level), change collations, compute/persist columns, switch partitions, enable/disable/manage triggers, etc., etc., etc...that's the part of the reason you don't see it enabled I'm sure...

HTH,

|||I understand your explanation but why have it visible for tables? Is this functionality only available for functions & stored procedures?|||

Well, the functionality to ALTER tables is available, you just don't get a fancy GUI menu option for it :-)...if you need to alter a table, you'll have to code the alter script yourself is all.

As for why to have it visible and disabled vs. invisible, not sure, would have to ask the GUI folks that one...probably just to be consistent with the options I guess...

HTH,

Alter table

Hi to all!
I have a silly problem, but I'm not able to solve it!
I know how to add a field in a table via Enterprise manager and I know also via ALTER TABLE command.
...But the problem is: I want to add a field NOT in the last position, but on the middle (for istance)
So, this is possible via Enterprise Manager but with the ALTER TABLE command? How can I add a field without put it in the last position?
...Thanks a lot!
Sergio

p.s.: Sorry for my poor english..I hope you understand my question!Do it in enterprise manager->design table, click on the little scroll with briefcase icon (thirs on the tool bar) and copy the generated SQL code from there, and exit design without saving.|||Originally posted by HanafiH
Do it in enterprise manager->design table, click on the little scroll with briefcase icon (thirs on the tool bar) and copy the generated SQL code from there, and exit design without saving.

THANKS A LOT! I SOLVED MY PROBLEM!..

I never take care about icons .........bur from NOW I will consider IT!
Once again thank you!
Sergio

Alter column with data

I am trying to use T-SQL to alter a column with data already in it from char to varbinary. This is very easy to do in Enterprise Manager, but just for my own knowledge I'm trying to figure out how to do this in T-SQL. I don't mind losing the data (I'm using a temp table to bring the converted data back in), but I want to keep the column in the same place. Here's what I have so far, but I keep getting an implicit conversion error:

UPDATE UserProfile

SET PassID = CAST(PassID AS VARBINARY(128))
GO
ALTER TABLE UserProfile

ALTER COLUMN PassID VARBINARY(128)
GO

In Enterprise Manager, there is an option to preview the code that will be executed. If you check that you will often find to make a change that is disallowed with simple alters and to keep the column order the same, the table is dropped and recreated. What makes the operation difficult, in the general case, is handling foreign key constraints.

Column order should never be relied on in Tables, but I can understand the desire from a documentation point of view.

The general approach for altering a column's datatype is to:

1. Drop any foreign key constraints.

2. Rename the table to a temporary name.

3. Recreate the table with the new definition.

4. Copy the data back -- setting identity insert if necessary.

5. Recreate all foreign key constraints. (4 and 5 can probably be switched.)

6. Drop the original table.

If you don't care about column order, you can rename the old column, add a new column with the different datatype, transfer the data, and drop the original column.

Allowing users access to their DB only?

Hey all.
I'm trying to set up SQL Server so that people with Enterprise Mgr can create a DB registration to their DB only (sql.yoursite.com). Are there any tutorials out there for doing this?
Thanks for the help!If they connect using SQL authentication, I'm pretty sure that they'll only see the databases that they can access.

-PatP|||Thanks for the info. Part of the problem is keeping users from creating a registration to the main SQL server na dlet them only get to their DB. In a shared hosting environment, you definately don't want people seeing all the DBs availble.|||There aren't any tutorials that I'm aware of. The problem with this is that you have to be a security administrator to add a user. My suggestion would be to put a procedure in your model database that allows people to add a user.

It would insert this user, the database name, and a datetime into a "utility database". You could have a job running on the server that hits that table every 15 minutes and adds users.

If you want to get fancy, you can have the stored procedure/form have different permission levels also, so they can have more granular control over what they let their users do. Although if you have this, you will want to have the ability to edit them also.

They would just have to know they need to wait 15 minutes or so after adding a new user for it to take effect.

Allowing access to Enterprise Manager without giving admin rights.

I have a user that will be doing specific updates to a specific table
in SQL. Rather than have them working at the server when these needed
to be done, I thought I would install SQL Admin Tools at their
workstation. Does anyone know if I can do this, and allow him use of
Enterprise Manager to access this table, without giving him admin
rights. He will need to import an excel file into this table
periodically.
Thanks.There's no need to give him Admin rights in order to update a table on
occaison.
Simply grant him insert/update/delete permissions to the specific table.
Then write some vb code to insert the data from Excel to SQL.
321686 HOW TO: Import Data into SQL Server from Excel
http://support.microsoft.com/?id=321686
Or
Create a DTS Package on the server. Have the user put his Excel file on a
server share, and then
periodically have the DTS package scheduled to run and process the data.
319951 HOW TO: Transfer Data to Excel by Using SQL Server Data
Transformation
http://support.microsoft.com/?id=319951
Or
You could simply give him db_datareader, db_datawriter in the database.
See Fixed Database Roles in SQL Books Online
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Allowing a user access to only a few tables

With MS SQL 2000 Enterprise Manager, is there a way to allow a user access
to only a few tables, but deny the user access to the rest without having to
go to all of the tables and denying access? The database has roughly 50
tables, but only 3 should be granted to the new user, so as you can see it
would be a painstaking task to manually do this with the *cough* mouse. Or,
if I can run some sort of grant script, that would work too. Thank you!SELECT 'DENY ALL|SELECT|INSERT|UPDATE|DELETE ON '+name+ ' TO <username>'
FROM SYSOBJECTS ORDER BY NAME

SELECT 'GRANT ALL|SELECT|INSERT|UPDATE|DELETE ON '+name+ ' TO
<username>' FROM SYSOBJECTS ORDER BY NAME

Highlight the results you want and run. You can alternate <username>
out for public or a specific group name. Good luck.

****************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com

Please remove NOMORESPAM before replying.

This posting is provided "as is" with
no warranties and confers no rights.

****************************************

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"Andy S." <andymcdba1@.NOMORESPAM.yahoo.com> wrote in message
news:40438ed0$0$195$75868355@.news.frii.net...
> SELECT 'DENY ALL|SELECT|INSERT|UPDATE|DELETE ON '+name+ ' TO <username>'
> FROM SYSOBJECTS ORDER BY NAME
> SELECT 'GRANT ALL|SELECT|INSERT|UPDATE|DELETE ON '+name+ ' TO
> <username>' FROM SYSOBJECTS ORDER BY NAME
> Highlight the results you want and run. You can alternate <username>
> out for public or a specific group name. Good luck.
> ****************************************
> Andy S.
> MCSE NT/2000, MCDBA SQL 7/2000
> andymcdba1@.NOMORESPAM.yahoo.com
> Please remove NOMORESPAM before replying.
> This posting is provided "as is" with
> no warranties and confers no rights.
> ****************************************
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Hello Andy,

Thank you for the help. I've run that script, and in the Expr1 column the
first line is:
DENY ALL|SELECT|INSERT|UPDATE|DELETE ON Aliases TO vms

So I have selected the line and selected "run" from the list. Is this the
propper way of denying the user "vms" to the aliases table? The reason I ask
is because when I check the permissions on that table, the user still has
all options (select, insert, etc.) checked. Again, thank you for the help!

Allow access through network

Hi,

How can i make Query analyzer access SQL Server in a network, I've already allowed netowrk connections in enterprise manager(but locally) and none of the users could access what can it be ?

Thanks

Hi,

didi you enable a listerner for TCP/IP ? Enable this protocol and you will get access to the services of SQL Server.

-Jens Suessmeyer.

http://www.sqlserver2005.de

|||

It worked thanks

Thanks

all stored procedures are user stored procedures

Looking (in Enterprise Manager) at the stored procedure list of a database
yesterday, I noticed that all the procedures were listed as "user"
procedures, including dt_addtosourcecontrol and the like, which are usually
system procedures, and on every other database on the server are system
procedures.
Looking at sysobjects:
select name, objectproperty(id,'IsMSShipped') as is_system from sysobjects
where type = 'P'
I get nothing but zeros for is_system, consistent with what EM shows (and
different from what I get in other databases.
I'm pretty (but not absolutely) sure that a w or two ago, this database
listed dt_addtosourcecontrol and the like as system stored procedures.
Supporting this, the creation dates of the dt_... procedures are:
1. all the same, and
2. different from the creation date of the database.
So, I'm guessing that something I (or someone) did, changed them into user
stored procedures. But unless someone deliberately fooled with the system
tables just to confuse be, I'm unsure as to what it could be. Does anyone
have any ideas?Hi
You may want to compare the other entries in sysobjects between two
different databases.
John
"Thomas Berg" wrote:

> Looking (in Enterprise Manager) at the stored procedure list of a database
> yesterday, I noticed that all the procedures were listed as "user"
> procedures, including dt_addtosourcecontrol and the like, which are usuall
y
> system procedures, and on every other database on the server are system
> procedures.
> Looking at sysobjects:
> select name, objectproperty(id,'IsMSShipped') as is_system from sysobjec
ts
> where type = 'P'
> I get nothing but zeros for is_system, consistent with what EM shows (and
> different from what I get in other databases.
> I'm pretty (but not absolutely) sure that a w or two ago, this database
> listed dt_addtosourcecontrol and the like as system stored procedures.
> Supporting this, the creation dates of the dt_... procedures are:
> 1. all the same, and
> 2. different from the creation date of the database.
> So, I'm guessing that something I (or someone) did, changed them into user
> stored procedures. But unless someone deliberately fooled with the system
> tables just to confuse be, I'm unsure as to what it could be. Does anyone
> have any ideas?
>

All Hardware Being Equal

Does SQL Server 2005 Express perform equal to SQL Server 2005 Enterprise? Basically, if the hardware that both editions were deployed on was identical and configured with in conjunction with the hardware limitations that Express imposes, would the two editions perform the same when running the same querries?

I have read that SQL Express uses the same database engine but does this mean that the Express edition will perform as well as the Enterprise edition?

SQL Express has certain limits. Only 1 CPU is used, only 1GB of RAM is used, no 64-bit support, databases can't be larger than 4GB. If you're comparing SQL Express to SQL Enterprise on a hardware above these things, Enterprise will definitely be faster as it doesn't have such limits.

From a software standpoint, the only performance feature difference I'm aware of is that SQL Enterprise will consider Indexed Views when optimizing queries... any lesser version (inlcuding SQL Standard) will not do this.

There may be differences in the default configuration of the editions, too.

So, as you can see, it's a bit tricky to get an apples-to-apples comparison :-) All things considered, though, the performance should be pretty close on small databases.

-Ryan / Kardax

Alignment problem with SQL Server 2000

Greeting
I have more than one language installed on my computer, and I tried to use my Enterprise Manager yesterday... What Im getting is with all the GUI im using Im having everything is Right Aligned....
Any help as to what I should change?
Thanks
This is my first post so would be great to have a qucik and handy answer :D??? :confused:

Can you attach a screenshot?

What country are you in?

blindman|||Here we go
Australia...I have English,Arabic,Japanese,Chinese installed|||Strange, in the Australian version everything is supposed to be upside-down, not reversed left-right.

.
.
.
Couldn't resist. :D

I'll see if I can find anything that might have caused this, but I've never seen it before.|||Dig hard man.. Its realy sucks to work with such alignment.|||I suspect this is a result of server settings on your platform.

What regional options do you have for your Locale and your Default Language?|||You mean like this ... go to control panel ... regional settings ... General tab ... and set english to default. :D ;) :p :).|||Nope,
Checked it now.. Hope it was the case.. I have English(Australia) as the default language... Im running XP if that helps...|||Originally posted by blindman
I suspect this is a result of server settings on your platform.

What regional options do you have for your Locale and your Default Language?
Hi,
Guess what you are right, its because Australia, hence the up side down :D Nah kidding
It wasnt the default language that wasnt set to English it was the Language for non-unicode Programs that wasnt set to English.
Cheers mate !!