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 a connection manager dynamically via a variable

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

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

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

Any ideas why this isn’t working?

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

-Jamie

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

Alter table via t-sql vs Ent Manager

Hello all!
What is the difference between running a alter table statement that adds a
new column to a sql server 2000 table using Query Analyser or Enterprise
Manager?
I noticed that if we do it using Ent. Manager it creates a big script that
copies the whole table to a temp table, adds the new column and than
renames it to the original table dropping the old one.
If we do it using QA we just need a "ALTER TABLE X ADD ..."
Question IS:
Is EM preferable, more reliable?
Is QA safe in the case of a server failure while running the query. What is
the fastest/reliable method if I want to add a column to a 13 milion record
table?. BOL states that alter table statements are logged a fully
recoverable. This recovery would be automatic or would require any manual
statements?
Very confused as you see...
TIA
mid
Hi.
Difference:
If you do change in table structure using EM then:-
1. Unload the data
2. Drop the table and dependants (indexes)
3. Create the table with new definition
4. Create dependant objects
5. Loads back the data and delete the file.
If You do it Query Analyzer using ALTER TABLE then:-
1. It just directly changes the definition or adds a new column.
So it is allways advisible to change the table definition using Query
ANalyzer -- ALTER TABLE command. Since this activity is
logged we can revert back if the database is set in FULL recovery model.
As well as it is very fast, only disadvantage is using alter Table you cann
add a new column only at the last.
Thanks
Hari
MCDBA
"mid" <midbarsinai@.midbarnospam.org> wrote in message
news:1ar8ck4gwbl0l.dlg@.midbarnospam.org...
> Hello all!
> What is the difference between running a alter table statement that adds a
> new column to a sql server 2000 table using Query Analyser or Enterprise
> Manager?
> I noticed that if we do it using Ent. Manager it creates a big script that
> copies the whole table to a temp table, adds the new column and than
> renames it to the original table dropping the old one.
> If we do it using QA we just need a "ALTER TABLE X ADD ..."
> Question IS:
> Is EM preferable, more reliable?
> Is QA safe in the case of a server failure while running the query. What
is
> the fastest/reliable method if I want to add a column to a 13 milion
record
> table?. BOL states that alter table statements are logged a fully
> recoverable. This recovery would be automatic or would require any manual
> statements?
> Very confused as you see...
> TIA
> mid
|||Thanks Hari.
My database is in Simple recovery model. Supose I am running a long alter
table statement via QA and sql server stops. When I bring SQL online again
will the structure of the table reflect the old structure? Will I have a
corrupted table?
When you say "we can revert back if the database is set in FULL recovery
model" I think you mean that if needed we can have the old structure back
again by aplying the last Transaction Log backup. Am I correct?
Thanks again,
mid
On Thu, 20 May 2004 15:14:17 +0530, Hari wrote:
[vbcol=seagreen]
> Hi.
> Difference:
> If you do change in table structure using EM then:-
> 1. Unload the data
> 2. Drop the table and dependants (indexes)
> 3. Create the table with new definition
> 4. Create dependant objects
> 5. Loads back the data and delete the file.
>
> If You do it Query Analyzer using ALTER TABLE then:-
> 1. It just directly changes the definition or adds a new column.
>
> So it is allways advisible to change the table definition using Query
> ANalyzer -- ALTER TABLE command. Since this activity is
> logged we can revert back if the database is set in FULL recovery model.
> As well as it is very fast, only disadvantage is using alter Table you cann
> add a new column only at the last.
> Thanks
> Hari
> MCDBA
>
>
>
> "mid" <midbarsinai@.midbarnospam.org> wrote in message
> news:1ar8ck4gwbl0l.dlg@.midbarnospam.org...
> is
> record
|||Hi,
Restarting while doing a ALTER TABLE (Any DDL statement) is bit risky. The
table might not get corrupted rather the change
will be rolled back once the SQL Server comes back. So you can get the old
image of table.
This scenario will be same for SIMPLE or FULL recovery model. In FULL
recovery model even if the ALTER TABLE succeedes you can roll back
to old shape.
But doing this is very risky.
Thanks
Hari
MCDBA
"mid" <midbarsinai@.midbarnospam.org> wrote in message
news:3ye70mka23ys$.dlg@.midbarnospam.org...[vbcol=seagreen]
> Thanks Hari.
> My database is in Simple recovery model. Supose I am running a long alter
> table statement via QA and sql server stops. When I bring SQL online again
> will the structure of the table reflect the old structure? Will I have a
> corrupted table?
> When you say "we can revert back if the database is set in FULL recovery
> model" I think you mean that if needed we can have the old structure back
> again by aplying the last Transaction Log backup. Am I correct?
> Thanks again,
> mid
> On Thu, 20 May 2004 15:14:17 +0530, Hari wrote:
cann[vbcol=seagreen]
adds a[vbcol=seagreen]
Enterprise[vbcol=seagreen]
that[vbcol=seagreen]
What[vbcol=seagreen]
manual[vbcol=seagreen]
sql

Alter table via t-sql vs Ent Manager

Hello all!
What is the difference between running a alter table statement that adds a
new column to a sql server 2000 table using Query Analyser or Enterprise
Manager?
I noticed that if we do it using Ent. Manager it creates a big script that
copies the whole table to a temp table, adds the new column and than
renames it to the original table dropping the old one.
If we do it using QA we just need a "ALTER TABLE X ADD ..."
Question IS:
Is EM preferable, more reliable?
Is QA safe in the case of a server failure while running the query. What is
the fastest/reliable method if I want to add a column to a 13 milion record
table?. BOL states that alter table statements are logged a fully
recoverable. This recovery would be automatic or would require any manual
statements?
Very confused as you see...
TIA
midHi.
Difference:
If you do change in table structure using EM then:-
1. Unload the data
2. Drop the table and dependants (indexes)
3. Create the table with new definition
4. Create dependant objects
5. Loads back the data and delete the file.
If You do it Query Analyzer using ALTER TABLE then:-
1. It just directly changes the definition or adds a new column.
So it is allways advisible to change the table definition using Query
ANalyzer -- ALTER TABLE command. Since this activity is
logged we can revert back if the database is set in FULL recovery model.
As well as it is very fast, only disadvantage is using alter Table you cann
add a new column only at the last.
Thanks
Hari
MCDBA
"mid" <midbarsinai@.midbarnospam.org> wrote in message
news:1ar8ck4gwbl0l.dlg@.midbarnospam.org...
> Hello all!
> What is the difference between running a alter table statement that adds a
> new column to a sql server 2000 table using Query Analyser or Enterprise
> Manager?
> I noticed that if we do it using Ent. Manager it creates a big script that
> copies the whole table to a temp table, adds the new column and than
> renames it to the original table dropping the old one.
> If we do it using QA we just need a "ALTER TABLE X ADD ..."
> Question IS:
> Is EM preferable, more reliable?
> Is QA safe in the case of a server failure while running the query. What
is
> the fastest/reliable method if I want to add a column to a 13 milion
record
> table?. BOL states that alter table statements are logged a fully
> recoverable. This recovery would be automatic or would require any manual
> statements?
> Very confused as you see...
> TIA
> mid|||Thanks Hari.
My database is in Simple recovery model. Supose I am running a long alter
table statement via QA and sql server stops. When I bring SQL online again
will the structure of the table reflect the old structure? Will I have a
corrupted table?
When you say "we can revert back if the database is set in FULL recovery
model" I think you mean that if needed we can have the old structure back
again by aplying the last Transaction Log backup. Am I correct?
Thanks again,
mid
On Thu, 20 May 2004 15:14:17 +0530, Hari wrote:
> Hi.
> Difference:
> If you do change in table structure using EM then:-
> 1. Unload the data
> 2. Drop the table and dependants (indexes)
> 3. Create the table with new definition
> 4. Create dependant objects
> 5. Loads back the data and delete the file.
>
> If You do it Query Analyzer using ALTER TABLE then:-
> 1. It just directly changes the definition or adds a new column.
>
> So it is allways advisible to change the table definition using Query
> ANalyzer -- ALTER TABLE command. Since this activity is
> logged we can revert back if the database is set in FULL recovery model.
> As well as it is very fast, only disadvantage is using alter Table you cann
> add a new column only at the last.
> Thanks
> Hari
> MCDBA
>
>
>
> "mid" <midbarsinai@.midbarnospam.org> wrote in message
> news:1ar8ck4gwbl0l.dlg@.midbarnospam.org...
>> Hello all!
>> What is the difference between running a alter table statement that adds a
>> new column to a sql server 2000 table using Query Analyser or Enterprise
>> Manager?
>> I noticed that if we do it using Ent. Manager it creates a big script that
>> copies the whole table to a temp table, adds the new column and than
>> renames it to the original table dropping the old one.
>> If we do it using QA we just need a "ALTER TABLE X ADD ..."
>> Question IS:
>> Is EM preferable, more reliable?
>> Is QA safe in the case of a server failure while running the query. What
> is
>> the fastest/reliable method if I want to add a column to a 13 milion
> record
>> table?. BOL states that alter table statements are logged a fully
>> recoverable. This recovery would be automatic or would require any manual
>> statements?
>> Very confused as you see...
>> TIA
>> mid|||Hi,
Restarting while doing a ALTER TABLE (Any DDL statement) is bit risky. The
table might not get corrupted rather the change
will be rolled back once the SQL Server comes back. So you can get the old
image of table.
This scenario will be same for SIMPLE or FULL recovery model. In FULL
recovery model even if the ALTER TABLE succeedes you can roll back
to old shape.
But doing this is very risky.
Thanks
Hari
MCDBA
"mid" <midbarsinai@.midbarnospam.org> wrote in message
news:3ye70mka23ys$.dlg@.midbarnospam.org...
> Thanks Hari.
> My database is in Simple recovery model. Supose I am running a long alter
> table statement via QA and sql server stops. When I bring SQL online again
> will the structure of the table reflect the old structure? Will I have a
> corrupted table?
> When you say "we can revert back if the database is set in FULL recovery
> model" I think you mean that if needed we can have the old structure back
> again by aplying the last Transaction Log backup. Am I correct?
> Thanks again,
> mid
> On Thu, 20 May 2004 15:14:17 +0530, Hari wrote:
> > Hi.
> >
> > Difference:
> >
> > If you do change in table structure using EM then:-
> >
> > 1. Unload the data
> > 2. Drop the table and dependants (indexes)
> > 3. Create the table with new definition
> > 4. Create dependant objects
> > 5. Loads back the data and delete the file.
> >
> >
> > If You do it Query Analyzer using ALTER TABLE then:-
> >
> > 1. It just directly changes the definition or adds a new column.
> >
> >
> > So it is allways advisible to change the table definition using Query
> > ANalyzer -- ALTER TABLE command. Since this activity is
> > logged we can revert back if the database is set in FULL recovery model.
> > As well as it is very fast, only disadvantage is using alter Table you
cann
> > add a new column only at the last.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> >
> >
> > "mid" <midbarsinai@.midbarnospam.org> wrote in message
> > news:1ar8ck4gwbl0l.dlg@.midbarnospam.org...
> >> Hello all!
> >>
> >> What is the difference between running a alter table statement that
adds a
> >> new column to a sql server 2000 table using Query Analyser or
Enterprise
> >> Manager?
> >>
> >> I noticed that if we do it using Ent. Manager it creates a big script
that
> >> copies the whole table to a temp table, adds the new column and than
> >> renames it to the original table dropping the old one.
> >>
> >> If we do it using QA we just need a "ALTER TABLE X ADD ..."
> >>
> >> Question IS:
> >> Is EM preferable, more reliable?
> >> Is QA safe in the case of a server failure while running the query.
What
> > is
> >> the fastest/reliable method if I want to add a column to a 13 milion
> > record
> >> table?. BOL states that alter table statements are logged a fully
> >> recoverable. This recovery would be automatic or would require any
manual
> >> statements?
> >>
> >> Very confused as you see...
> >>
> >> TIA
> >> mid

Alter table via t-sql vs Ent Manager

Hello all!
What is the difference between running a alter table statement that adds a
new column to a sql server 2000 table using Query Analyser or Enterprise
Manager?
I noticed that if we do it using Ent. Manager it creates a big script that
copies the whole table to a temp table, adds the new column and than
renames it to the original table dropping the old one.
If we do it using QA we just need a "ALTER TABLE X ADD ..."
Question IS:
Is EM preferable, more reliable?
Is QA safe in the case of a server failure while running the query. What is
the fastest/reliable method if I want to add a column to a 13 milion record
table?. BOL states that alter table statements are logged a fully
recoverable. This recovery would be automatic or would require any manual
statements?
Very confused as you see...
TIA
midHi.
Difference:
If you do change in table structure using EM then:-
1. Unload the data
2. Drop the table and dependants (indexes)
3. Create the table with new definition
4. Create dependant objects
5. Loads back the data and delete the file.
If You do it Query Analyzer using ALTER TABLE then:-
1. It just directly changes the definition or adds a new column.
So it is allways advisible to change the table definition using Query
ANalyzer -- ALTER TABLE command. Since this activity is
logged we can revert back if the database is set in FULL recovery model.
As well as it is very fast, only disadvantage is using alter Table you cann
add a new column only at the last.
Thanks
Hari
MCDBA
"mid" <midbarsinai@.midbarnospam.org> wrote in message
news:1ar8ck4gwbl0l.dlg@.midbarnospam.org...
> Hello all!
> What is the difference between running a alter table statement that adds a
> new column to a sql server 2000 table using Query Analyser or Enterprise
> Manager?
> I noticed that if we do it using Ent. Manager it creates a big script that
> copies the whole table to a temp table, adds the new column and than
> renames it to the original table dropping the old one.
> If we do it using QA we just need a "ALTER TABLE X ADD ..."
> Question IS:
> Is EM preferable, more reliable?
> Is QA safe in the case of a server failure while running the query. What
is
> the fastest/reliable method if I want to add a column to a 13 milion
record
> table?. BOL states that alter table statements are logged a fully
> recoverable. This recovery would be automatic or would require any manual
> statements?
> Very confused as you see...
> TIA
> mid|||Thanks Hari.
My database is in Simple recovery model. Supose I am running a long alter
table statement via QA and sql server stops. When I bring SQL online again
will the structure of the table reflect the old structure? Will I have a
corrupted table?
When you say "we can revert back if the database is set in FULL recovery
model" I think you mean that if needed we can have the old structure back
again by aplying the last Transaction Log backup. Am I correct?
Thanks again,
mid
On Thu, 20 May 2004 15:14:17 +0530, Hari wrote:
[vbcol=seagreen]
> Hi.
> Difference:
> If you do change in table structure using EM then:-
> 1. Unload the data
> 2. Drop the table and dependants (indexes)
> 3. Create the table with new definition
> 4. Create dependant objects
> 5. Loads back the data and delete the file.
>
> If You do it Query Analyzer using ALTER TABLE then:-
> 1. It just directly changes the definition or adds a new column.
>
> So it is allways advisible to change the table definition using Query
> ANalyzer -- ALTER TABLE command. Since this activity is
> logged we can revert back if the database is set in FULL recovery model.
> As well as it is very fast, only disadvantage is using alter Table you can
n
> add a new column only at the last.
> Thanks
> Hari
> MCDBA
>
>
>
> "mid" <midbarsinai@.midbarnospam.org> wrote in message
> news:1ar8ck4gwbl0l.dlg@.midbarnospam.org...
> is
> record|||Hi,
Restarting while doing a ALTER TABLE (Any DDL statement) is bit risky. The
table might not get corrupted rather the change
will be rolled back once the SQL Server comes back. So you can get the old
image of table.
This scenario will be same for SIMPLE or FULL recovery model. In FULL
recovery model even if the ALTER TABLE succeedes you can roll back
to old shape.
But doing this is very risky.
Thanks
Hari
MCDBA
"mid" <midbarsinai@.midbarnospam.org> wrote in message
news:3ye70mka23ys$.dlg@.midbarnospam.org...[vbcol=seagreen]
> Thanks Hari.
> My database is in Simple recovery model. Supose I am running a long alter
> table statement via QA and sql server stops. When I bring SQL online again
> will the structure of the table reflect the old structure? Will I have a
> corrupted table?
> When you say "we can revert back if the database is set in FULL recovery
> model" I think you mean that if needed we can have the old structure back
> again by aplying the last Transaction Log backup. Am I correct?
> Thanks again,
> mid
> On Thu, 20 May 2004 15:14:17 +0530, Hari wrote:
>
cann[vbcol=seagreen]
adds a[vbcol=seagreen]
Enterprise[vbcol=seagreen]
that[vbcol=seagreen]
What[vbcol=seagreen]
manual[vbcol=seagreen]

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 name

Is there a way to alter a table name through query analyser.
I know how to do it through enteprise manager but would like to create a job
or package that runs daily and renames an existing table and creates a new
one.You can use sp_rename, see Books Online...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:OB1G5zR4EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Is there a way to alter a table name through query analyser.
> I know how to do it through enteprise manager but would like to create a
job
> or package that runs daily and renames an existing table and creates a new
> one.
>|||check out sp_rename in BOL.
--
Andrew J. Kelly SQL MVP
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:OB1G5zR4EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Is there a way to alter a table name through query analyser.
> I know how to do it through enteprise manager but would like to create a
> job or package that runs daily and renames an existing table and creates a
> new one.
>|||You can change object names with sp_rename. A simple example:
EXEC sp_rename 'Table1', 'Table1_Old'
GO
CREATE TABLE Table1
(
MyData int
)
GO
Note that renaming a table will not rename the associated constraints or
triggers. You'll need to rename those object too before recreating.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:OB1G5zR4EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Is there a way to alter a table name through query analyser.
> I know how to do it through enteprise manager but would like to create a
> job or package that runs daily and renames an existing table and creates a
> new one.
>

Alter table name

Is there a way to alter a table name through query analyser.
I know how to do it through enteprise manager but would like to create a job
or package that runs daily and renames an existing table and creates a new
one.
You can use sp_rename, see Books Online...
http://www.aspfaq.com/
(Reverse address to reply.)
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:OB1G5zR4EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Is there a way to alter a table name through query analyser.
> I know how to do it through enteprise manager but would like to create a
job
> or package that runs daily and renames an existing table and creates a new
> one.
>
|||check out sp_rename in BOL.
Andrew J. Kelly SQL MVP
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:OB1G5zR4EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Is there a way to alter a table name through query analyser.
> I know how to do it through enteprise manager but would like to create a
> job or package that runs daily and renames an existing table and creates a
> new one.
>
|||You can change object names with sp_rename. A simple example:
EXEC sp_rename 'Table1', 'Table1_Old'
GO
CREATE TABLE Table1
(
MyData int
)
GO
Note that renaming a table will not rename the associated constraints or
triggers. You'll need to rename those object too before recreating.
Hope this helps.
Dan Guzman
SQL Server MVP
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:OB1G5zR4EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Is there a way to alter a table name through query analyser.
> I know how to do it through enteprise manager but would like to create a
> job or package that runs daily and renames an existing table and creates a
> new one.
>

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 user to connect to his database

Hi
I would like to give user access to his database running on mssql 2000 using
enterprise manager.
But... the user must not be able to see any other databases on that server,
I just want him to be able to see his own database and manage it.
Is this possible without installing another instance of sql server and give
that user access to that instance?
and if so how does the licensing work if you have many instances of sql
running on one server?
Regards
GauiThat's a known limitation with Enterprise Manager. It was designed for the
system admin to use to manage all databases.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Just trying to do this aswell.. Never mind will have to
find another way. Do you know if there is a seperate tool
for visually creating stored procedures other than VIEW /
SP parts of Enterprise manager?

>--Original Message--
>That's a known limitation with Enterprise Manager. It
was designed for the
>system admin to use to manage all databases.
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>

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 null in a field in Flat File Source

How could I specify in either FF Connection manager or source that it shouldnt give any error and assume blank or no value as NULL ?

Thanks,
FahadYou can't. There is no such thing in a flat file. You'll have to bring in your empty field and then use a derived column to set it to NULL, if necessary.|||Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?|||

Fahad349 wrote:

Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?

You can do all of this in a derived column. You can't change the type of a column in the dataflow, but you can CAST it to a new type in a NEW column.|||Ok, Last thing is, the source feed contains spaces instead of no value between 2 commas, and I think this makes FF Source failed, what do you think ?|||Import as string, then you can TRIM() that field later in a derived column.|||

you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.

I think that solves your problem.

|||

Saurabh Kulkarni wrote:

you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.

I think that solves your problem.

Except the OP stated that the value was blank or empty, not null (char(0)).

Allow null in a field in Flat File Source

How could I specify in either FF Connection manager or source that it shouldnt give any error and assume blank or no value as NULL ?

Thanks,
FahadYou can't. There is no such thing in a flat file. You'll have to bring in your empty field and then use a derived column to set it to NULL, if necessary.|||Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?|||

Fahad349 wrote:

Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?

You can do all of this in a derived column. You can't change the type of a column in the dataflow, but you can CAST it to a new type in a NEW column.|||Ok, Last thing is, the source feed contains spaces instead of no value between 2 commas, and I think this makes FF Source failed, what do you think ?|||Import as string, then you can TRIM() that field later in a derived column.|||

you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.

I think that solves your problem.

|||

Saurabh Kulkarni wrote:

you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.

I think that solves your problem.

Except the OP stated that the value was blank or empty, not null (char(0)).

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?
>

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 !!