I have executed a data script using query analyser and closed the analyser without commiting the transaction.
I want to know are there any pending transactions still.
thanks
Quote:
Originally Posted by kvm
Hi
I have executed a data script using query analyser and closed the analyser without commiting the transaction.
I want to know are there any pending transactions still.
thanks
I am using a script component to transform data. In the script component I created a bunch of fields for the output. Is there any way to loop through that list of columns? Is there code I can use in the script component to access the names, data types, data etc?
I saw a lot of informaiton on the OutputColumnCollection as part of some IDTSOuput90 thing (greek to me). As best I can guess this is for creating your own new columns, but can I see what columns are already defined via the script interface?
I was able to get a specific column by doing this...
Dim o As IDTSOutput90
o = ComponentMetaData.OutputCollection.GetObjectByID(88)
MsgBox("Column Count: " & o.OutputColumnCollection.Count)
MsgBox("Colum: " & o.OutputColumnCollection.FindObjectByID(139).Name)
But Is there a way to just loop through all the ids? I have to know the id to stick in findobjectbyid(x). Can i do a loop and have x = all the ids? Additionally the 88 is hard code value... is there a way to get the current or specific output set?
The ultimate goal is to loop through the column collection and set a column = value. Basically I have an array of values and want to loop through the columns and set it to the array postion.
|||Ok... sorry about the multiple posts here... but I got some more logic to work...
I was able to get able to get columns by doing this...
Dim o As IDTSOutput90
o = ComponentMetaData.OutputCollection.GetObjectByID(88)
For Each col As IDTSOutputColumn90 In o.OutputColumnCollection
MsgBox("Name: " & col.Name)
Next
So the updated question is this...
The ultimate goal is to loop through the column collection and set a column = value. Basically I have an array of values and want to loop through the columns and set it to the array postions value. I can't find how to set the column value equal to something while i loop through it. Additionally... is there a reference or good way to get the data type? When i use the col.datatype is returns a number vs description. I want to have logic when it set the value to do coverts based on the data type.
I have a database I need to supply something (I'm assuming a t-sql script..
maybe something else is better) to update customer tables with.
The operations include mostly changing varchar lengths, though a couple of
columns were renamed.
I'd like to maybe figure out how to get Enterprise Manager or Query Analyzer
to generate the scripts.
I can't just send alter table scripts because I'm involving all sorts of
constraints that have to be disabled/or dropped, the alter made, then have
them enabled/ or re-created.
Basically I'm hoping to get the tools to do the rather large amount of work
for me. I'm targetting sql server 2000.
Can someone make a knowledgeable suggestion?
Regards
Jeff KishJeff Kish wrote:
> Hi.
> I have a database I need to supply something (I'm assuming a t-sql script..
> maybe something else is better) to update customer tables with.
> The operations include mostly changing varchar lengths, though a couple of
> columns were renamed.
Not a good idea IMHO although you can use sp_rename.
> I'd like to maybe figure out how to get Enterprise Manager or Query Analyzer
> to generate the scripts.
> I can't just send alter table scripts because I'm involving all sorts of
> constraints that have to be disabled/or dropped, the alter made, then have
> them enabled/ or re-created.
Then generate the SQL for the target state and insert drops yourself.
> Basically I'm hoping to get the tools to do the rather large amount of work
> for me. I'm targetting sql server 2000.
> Can someone make a knowledgeable suggestion?
I don't think you will be able to get this out of EM - at least not
directly. It would basically mean to trace your operations and generate
SQL from that. I don't think EM will do that for such a complex set of
operations. You'll have to do some manual work.
Kind regards
robert|||Jeff Kish (jeff.kish@.mro.com) writes:
> I have a database I need to supply something (I'm assuming a t-sql
> script.. maybe something else is better) to update customer tables with.
> The operations include mostly changing varchar lengths, though a couple
> of columns were renamed.
> I'd like to maybe figure out how to get Enterprise Manager or Query
> Analyzer to generate the scripts.
> I can't just send alter table scripts because I'm involving all sorts of
> constraints that have to be disabled/or dropped, the alter made, then have
> them enabled/ or re-created.
> Basically I'm hoping to get the tools to do the rather large amount of
> work for me. I'm targetting sql server 2000.
Composing change scripts for production environments is not a task to
take lightly. Particularly not if you have to apply them while the system
is operating. (If the system closes for business, you may be able to repair
a disaster by restorin a backup.)
It requires good understanding of what can go wrong, and how to prevent
that. For instance, if you need to drop constraints to alter a column,
you should probably wrap that in a transaction, so you don't end up with
losing the constraint.
At the same time, ALTER operations that require changes to the physical
data pages, can take a huge toll on the transaction log, causing it to
grow rapidly. (Changing varchar lengths should be metadata so that should
be safe.)
You can use Enterprise Manager to have it to generate change scripts.
However, there are several flaw in those scripts, and you need to review
them carefully, and also make several changes to them. For instance, the
transaction scope in those scripts are wacko.
What may prove to be a show-stopper is that EM works with SQL 6.5 as its
target DB (same in Mgmt Studio in SQL 2005, by the way). 6.5 did not
have ALTER TABLE ALTER COLUMN, so I would guess that it implements the
update as create new table and copy data over. Which sometimes is the right
thing, but not when ALTER TABLE ALTER COLUMN is only a metadata change.
There are other tools on the market. Red Gate's SQL Compare get a lot
of positive acclaim, but I have not used it myself.
One potential problem is that you don't know the name of the constraints,
because they were added without a name, so all there is a system-generated
name. In this case, you need to retrieve the name, and then run DROP
CONSTRAINT dynamically. I would suggest that you restore the constraints
with a given name.
Speaking of constraints, make sure that you re-add them WITH CHECK. The
script from EM uses WITH NOCHEK, which means that they are not checked. This
is a lot faster, but it also means that the optimizer will neglect them,
which can have expensive consequences.
Finally, before you run in production, test on a copy of production!
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Fri, 24 Mar 2006 13:45:19 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:
>Jeff Kish (jeff.kish@.mro.com) writes:
>> I have a database I need to supply something (I'm assuming a t-sql
<snip>
>Composing change scripts for production environments is not a task to
>take lightly. Particularly not if you have to apply them while the system
>is operating. (If the system closes for business, you may be able to repair
>a disaster by restorin a backup.)
>It requires good understanding of what can go wrong, and how to prevent
>that. For instance, if you need to drop constraints to alter a column,
>you should probably wrap that in a transaction, so you don't end up with
>losing the constraint.
ahh.. I had no idea that transactions could wrap/rollback ddl. I don't think
that is the case in Oracle.
>At the same time, ALTER operations that require changes to the physical
>data pages, can take a huge toll on the transaction log, causing it to
>grow rapidly. (Changing varchar lengths should be metadata so that should
>be safe.)
>You can use Enterprise Manager to have it to generate change scripts.
>However, there are several flaw in those scripts, and you need to review
>them carefully, and also make several changes to them. For instance, the
>transaction scope in those scripts are wacko.
Can you tell me how? I'm having some problem seeing how to get the equivalent
alter table etc scripts out of EM. I looked, honest. I'll even look some more.
>What may prove to be a show-stopper is that EM works with SQL 6.5 as its
>target DB (same in Mgmt Studio in SQL 2005, by the way). 6.5 did not
>have ALTER TABLE ALTER COLUMN, so I would guess that it implements the
>update as create new table and copy data over. Which sometimes is the right
>thing, but not when ALTER TABLE ALTER COLUMN is only a metadata change.
target is only sql server 2000 right now.
>There are other tools on the market. Red Gate's SQL Compare get a lot
>of positive acclaim, but I have not used it myself.
>One potential problem is that you don't know the name of the constraints,
>because they were added without a name, so all there is a system-generated
>name. In this case, you need to retrieve the name, and then run DROP
>CONSTRAINT dynamically. I would suggest that you restore the constraints
>with a given name.
mmm not sure I understand.. they are originally added specifically. can I just
disable them or do I need to drop them?
>Speaking of constraints, make sure that you re-add them WITH CHECK. The
>script from EM uses WITH NOCHEK, which means that they are not checked. This
>is a lot faster, but it also means that the optimizer will neglect them,
>which can have expensive consequences.
>Finally, before you run in production, test on a copy of production!
of course! the scars I have should remind me of that. :> )
thanks so much.
Jeff Kish|||Jeff Kish (jeff.kish@.mro.com) writes:
>>You can use Enterprise Manager to have it to generate change scripts.
>>However, there are several flaw in those scripts, and you need to review
>>them carefully, and also make several changes to them. For instance, the
>>transaction scope in those scripts are wacko.
>>
> Can you tell me how? I'm having some problem seeing how to get the
> equivalent alter table etc scripts out of EM. I looked, honest. I'll
> even look some more.
Right-click table and select Modify Table. As I said, it is not likely
it will generate ALTER TABLE commands, those you will have to write
yourself. But at least you will get some code to recreate constraints.
Just be sure to change WITH NOCHECK to WITH CHECK.
>>One potential problem is that you don't know the name of the constraints,
>>because they were added without a name, so all there is a system-generated
>>name. In this case, you need to retrieve the name, and then run DROP
>>CONSTRAINT dynamically. I would suggest that you restore the constraints
>>with a given name.
>>
> mmm not sure I understand.. they are originally added specifically. can
> I just disable them or do I need to drop them?
The error message from ALTER TABLE makes it clear that you have to
drop the constraint. Keep in mind that a FK column must match the
PK column it refers to, so if you change a PK/FK pair, you need to
drop constraints for both columns before changing.
Yes, it follows that from what I say that you should mainly generate
the script manually. This may seem like a crazy idea, but since it's
so important that you understand what the script does, it can be
dangerous to leave that in the hands of a tool.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
I'm trying to generate scripts in SQL Server Management Studio 2005.
When I choose 'Script all objects' I get an error when I try to execute it. When I generate the scripts in single files, only tables in one file, only views in one file etc. etc., the execution is succeded.
1) Why do I get an error when I try to execute the script containing tables, views, procedures in one file....?
2) I get an error in the view-file where one column is 'invalid' but I can see it in the view. A generated script should execute succesfully when it is generated one second ago on the same database and so on... Right?
Thanks....
Which exact error do you get ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
sorry, I cant reproduce the errors because my workbuddy has the day off...
But I remember it was one of the views with 2 columns in it, which caused the error: 'Invalid item on view...'
And the sequense in the script is wrong. For example I can see that the script are trying to create a view before the table is created?
|||You may find that the Database Publishing Wizard offers you more functionality.
I have the same problem and Database Publishing Wizard does not work any better.
The CREATE VIEW is placed before the dependant tables in the script.
This ONLY happens when I'm trying to script a SQL Server 2005 Express db.
I run the script wizard in SQL Server Management Studio 2005.
SQL Server 2005 Express: 9.0.3042
SQL Server 2005: 9.0.1399
Thanks
Jonas
|||
Did you use the switch "create dependent objects" ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||Yes I did.
I've now updated to sp2 (9.0.3042) on the SQL Server 2005 too - no difference. Except that the default value of "create dependant objects" in the script wizard is now false.
I can not include any image of the properties selected, but all are default exept the dependant switch which is true.
The final report start with:
Generate Script Progress
- Determining objects in database '....MDF' that will be scripted. (Success)
- dbo.AllDataView (Success)
- dbo.FindNewRawData (Success)
Which also shows that a View is created first of all objects.
Thanks
Jonas
I'm trying to generate scripts in SQL Server Management Studio 2005.
When I choose 'Script all objects' I get an error when I try to execute it. When I generate the scripts in single files, only tables in one file, only views in one file etc. etc., the execution is succeded.
1) Why do I get an error when I try to execute the script containing tables, views, procedures in one file....?
2) I get an error in the view-file where one column is 'invalid' but I can see it in the view. A generated script should execute succesfully when it is generated one second ago on the same database and so on... Right?
Thanks....
Which exact error do you get ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
sorry, I cant reproduce the errors because my workbuddy has the day off...
But I remember it was one of the views with 2 columns in it, which caused the error: 'Invalid item on view...'
And the sequense in the script is wrong. For example I can see that the script are trying to create a view before the table is created?
|||You may find that the Database Publishing Wizard offers you more functionality.
I have the same problem and Database Publishing Wizard does not work any better.
The CREATE VIEW is placed before the dependant tables in the script.
This ONLY happens when I'm trying to script a SQL Server 2005 Express db.
I run the script wizard in SQL Server Management Studio 2005.
SQL Server 2005 Express: 9.0.3042
SQL Server 2005: 9.0.1399
Thanks
Jonas
|||
Did you use the switch "create dependent objects" ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||Yes I did.
I've now updated to sp2 (9.0.3042) on the SQL Server 2005 too - no difference. Except that the default value of "create dependant objects" in the script wizard is now false.
I can not include any image of the properties selected, but all are default exept the dependant switch which is true.
The final report start with:
Generate Script Progress
- Determining objects in database '....MDF' that will be scripted. (Success)
- dbo.AllDataView (Success)
- dbo.FindNewRawData (Success)
Which also shows that a View is created first of all objects.
Thanks
Jonas
I'm trying to generate scripts in SQL Server Management Studio 2005.
When I choose 'Script all objects' I get an error when I try to execute it. When I generate the scripts in single files, only tables in one file, only views in one file etc. etc., the execution is succeded.
1) Why do I get an error when I try to execute the script containing tables, views, procedures in one file....?
2) I get an error in the view-file where one column is 'invalid' but I can see it in the view. A generated script should execute succesfully when it is generated one second ago on the same database and so on... Right?
Thanks....
Which exact error do you get ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
sorry, I cant reproduce the errors because my workbuddy has the day off...
But I remember it was one of the views with 2 columns in it, which caused the error: 'Invalid item on view...'
And the sequense in the script is wrong. For example I can see that the script are trying to create a view before the table is created?
|||
You may find that the Database Publishing Wizard offers you more functionality.
|||Thomas, did you find a solution to your problem?
I have the same problem and Database Publishing Wizard does not work any better.
The CREATE VIEW is placed before the dependant tables in the script.
This ONLY happens when I'm trying to script a SQL Server 2005 Express db.
I run the script wizard in SQL Server Management Studio 2005.
SQL Server 2005 Express: 9.0.3042
SQL Server 2005: 9.0.1399
Thanks
Jonas
|||
Did you use the switch "create dependent objects" ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||Yes I did.
I've now updated to sp2 (9.0.3042) on the SQL Server 2005 too - no difference. Except that the default value of "create dependant objects" in the script wizard is now false.
I can not include any image of the properties selected, but all are default exept the dependant switch which is true.
The final report start with:
Generate Script Progress
- Determining objects in database '....MDF' that will be scripted. (Success)
- dbo.AllDataView (Success)
- dbo.FindNewRawData (Success)
Which also shows that a View is created first of all objects.
Thanks
Jonas
In SQL 2000 EM you were able to generate a script that only contained GRANT statements, (by unchecking the CREATE & DROP commands on the 2nd tab, and selecting Object Permissions on the 3rd tab). From what I can see in SQL 2005 MS you can't do this.
All that you can seem to do is generate a sciprt containing all the CREATE statements with the GRANT statement(s) after each object.
This is a bit of a pain as I want to script all the object permissions, without having to wade through hundreds of CREATE TABLE and CREATE PROCEDURE statements.
How can get just a script of GRANT statements?
Thanks.
It is not possible to generate scripts for object permissions through Management Studio like you could in Enterprise Manager. However, you can write a small SMO program to get the information the you need. This example should get you started on how to generate GRANT scripts for stored procedures.
ObjectPermissionInfo[] a = new ObjectPermissionInfo[5];
Database db = srv.Databases["pubs"];
foreach (StoredProcedure sp in db.StoredProcedures)
{
a = sp.EnumObjectPermissions();
foreach (ObjectPermissionInfo op in a)
{
Console.WriteLine("GRANT " + op.PermissionType.ToString() + " ON " + op.ObjectName.ToString() + " TO " + op.Grantee.ToString());
}
}
~Peter
|||Thanks for answering, I'll try what you suggest.I am taking my table names and colums from xml file and then generating the script and executing it.I have problem creating the script for incerting table with multiple colums.Maybe the problem is that I am using a for loop to get all column names from a array string.Has anyone idea why it is not working? When I try insert of a table without loop it is wirking!Here is the code :
Server server = new Server(); // Create table in my personal database Database db = server.Databases["new"]; // Make Sure Constraint is Scripted ScriptingOptions so = new ScriptingOptions(); so.IncludeHeaders = true; so.SchemaQualify = true; // Create In-Memory Table Table newTable = new Table(db, Names[0].ToString()); for (int i = 1; i < Names.Length; i++) { Column newColumn = new Column(newTable,Names
); newTable.Columns.Add(newColumn); } foreach (string s in newTable.Script(so)) /// Error generating the script { myTableScript = myTableScript + s; } db.ExecuteNonQuery(myTableScript); //disconnect form Database connection.Close();
Could you please metion the full error you are getting.
Thanks,
Kuntal
Hi,
I want to generate Script for 4 Stored Procedures at a time in SQL SERVER 2005 as 4 seperate files.
In SQL SERVER 2000 there is a option
-Script file per object, using which we can generate script only once for all the 4 sps as 4 seperate files
Is there any such option in 2005?
Please suggest.
Thanks in advance
Ram
They added this feature to the Scripting Wizard in SP2.|||
Jason Callas wrote:
They added this feature to the Scripting Wizard in SP2.
To elaborate a little bit more... if you use the Generate Script Wizard in SQL Server 2005 SP2, you can choose to create one file per object.
Steps:
1) Right-click on a database -> Tasks -> Generate Scripts...
2) On the "Output Option" screen
a) Choose "Script to file"
b) Choose "File per object"
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
sqlPreviosly, in SqlServer2000, when i was putting database changes (stored procedures) to the production server, I used to generate script for all stored procedures on the source server and run it it on the production server. It had in itself all the "If Exist--drop" rows, so it was posible to copy the new ones and change the existing stored procedures .
Now I'm using SqlServer2005 Express and when i want to generate sript, that "If Exist--drop" is missing so i have trouble changing all stored procedures in few steps. My question is: How can i get the script to contain those "If Exist--drop" automaticaly?
thanks
Yes. Management Studio uses the following logic,
When you create, "Script Behavior” = “Generate Create Statement Only”
If you say “Include If NOT Exists” option as TRUE then it Generate the following template
IF NOT EXISTS (SELECT * FROM ..
BEGIN
CREATE ..
END
When you create, "Script Behavior” = “Generate Drop Statement Only”
If you say “Include If NOT Exists” option as TRUE then it Generate the following template
IF EXISTS (SELECT * FROM ..
BEGIN
DROP ..
END
Missing in Console:
When you create, "Script Behavior” = “Generate Drop Statements Followed By Create Statement”
If you say “Include If NOT Exists” option as TRUE then it Generate the following template
IF EXISTS (SELECT * FROM ..
BEGIN
DROP ..
END
CREATE ..
Since the above option is missing (may be bug), first generate the drop script & append with Create generation Script with/without If Not Exist.
|||thanks|||(This is a related question, so I'll borrow the thread)
Is there a way to save the scripting options for the script wizard or customize the default options?
For various reasons (as this thread points out) I use script generaiton options changed from the defualts as:
Include Descriptive Headers = FALSE
Include If NOT EXISTS = True
Script Drop = True
Script USE DATABASE = FALSE
Script Indexes = True
Can I change my Script Generation Wizard defaults so I don't have to select these options each time?
|||
Is there a way to save the scripting options for the script wizard or customize the default options?
With SQL Server 2005 Service Pack 2, we've introduced the Tools -> Options... -> Scripting dialog so that you can change the behavior of how Management Studio generates scripts. These settings will be the default settings for the Generate Script Wizard.
Does this help?
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/
|||Paul, thanks for the tip about changing the script options.|||Hello,Not all of the options available during scripting appear to be configurable from Tools > Options.
I'm running 9.00.3042.00. Is there a hotfix that remedies this? Or is the selective availability by design? I'm mainly interested in the "Script Drop" option.
Vivek
Previosly, in SqlServer2000, when i was putting database changes (stored procedures) to the production server, I used to generate script for all stored procedures on the source server and run it it on the production server. It had in itself all the "If Exist--drop" rows, so it was posible to copy the new ones and change the existing stored procedures .
Now I'm using SqlServer2005 Express and when i want to generate sript, that "If Exist--drop" is missing so i have trouble changing all stored procedures in few steps. My question is: How can i get the script to contain those "If Exist--drop" automaticaly?
thanks
Yes. Management Studio uses the following logic,
When you create, "Script Behavior” = “Generate Create Statement Only”
If you say “Include If NOT Exists” option as TRUE then it Generate the following template
IF NOT EXISTS (SELECT * FROM ..
BEGIN
CREATE ..
END
When you create, "Script Behavior” = “Generate Drop Statement Only”
If you say “Include If NOT Exists” option as TRUE then it Generate the following template
IF EXISTS (SELECT * FROM ..
BEGIN
DROP ..
END
Missing in Console:
When you create, "Script Behavior” = “Generate Drop Statements Followed By Create Statement”
If you say “Include If NOT Exists” option as TRUE then it Generate the following template
IF EXISTS (SELECT * FROM ..
BEGIN
DROP ..
END
CREATE ..
Since the above option is missing (may be bug), first generate the drop script & append with Create generation Script with/without If Not Exist.
|||thanks|||(This is a related question, so I'll borrow the thread)
Is there a way to save the scripting options for the script wizard or customize the default options?
For various reasons (as this thread points out) I use script generaiton options changed from the defualts as:
Include Descriptive Headers = FALSE
Include If NOT EXISTS = True
Script Drop = True
Script USE DATABASE = FALSE
Script Indexes = True
Can I change my Script Generation Wizard defaults so I don't have to select these options each time?
|||
Is there a way to save the scripting options for the script wizard or customize the default options?
With SQL Server 2005 Service Pack 2, we've introduced the Tools -> Options... -> Scripting dialog so that you can change the behavior of how Management Studio generates scripts. These settings will be the default settings for the Generate Script Wizard.
Does this help?
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/
|||Paul, thanks for the tip about changing the script options.|||Hello,Not all of the options available during scripting appear to be configurable from Tools > Options.
I'm running 9.00.3042.00. Is there a hotfix that remedies this? Or is the selective availability by design? I'm mainly interested in the "Script Drop" option.
Vivek
