Hi All,
Whenever there is a timeout occured from Application which was connected to
SQL Server.
I tried to get the Blocking objects from SysProcesses table of master DB.
But most of the time it will clear very soon so i couldnt able to find the
actual objects which is Blocking.
Could any one suggests some ways to get the blocking objects.
Thanks in advance...
Regards,
Herbert R.SQL Server Profiler may help, but be reminded to log only the required
information as logging eats up resources.
References
- SQL Server 2000 Administrator's Pocket Consultant: Profiling and
Monitoring (Solving Performance Problems with Profiler)
http://www.microsoft.com/technet/pr...s/c10ppcsq.mspx
Martin C K Poon
Senior Analyst Programmer
====================================
"Herbert" <Herbert@.discussions.microsoft.com> bl
news:1E649539-6330-4D18-878C-276251E4F915@.microsoft.com g...
> Hi All,
> Whenever there is a timeout occured from Application which was connected
to
> SQL Server.
> I tried to get the Blocking objects from SysProcesses table of master DB.
> But most of the time it will clear very soon so i couldnt able to find the
> actual objects which is Blocking.
> Could any one suggests some ways to get the blocking objects.
> Thanks in advance...
> Regards,
> Herbert R.|||Check out http://support.microsoft.com/kb/271509/ for a script that can help
monitor blocking and record the objects involved.
Hope this helps.
Dan Guzman
SQL Server MVP
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:1E649539-6330-4D18-878C-276251E4F915@.microsoft.com...
> Hi All,
> Whenever there is a timeout occured from Application which was connected
> to
> SQL Server.
> I tried to get the Blocking objects from SysProcesses table of master DB.
> But most of the time it will clear very soon so i couldnt able to find the
> actual objects which is Blocking.
> Could any one suggests some ways to get the blocking objects.
> Thanks in advance...
> Regards,
> Herbert R.
Showing posts with label objects. Show all posts
Showing posts with label objects. Show all posts
Thursday, March 29, 2012
Get a list of the objects owned by a users
Hi,
My question may be stupid, but can;t figure out a way to do it "simply".
I'm taking over the admin of some SQL instance and I want to clenu up the
logins list. But before deleting anything, I need to know what objects are
owned by the user I want to delete to not brak anything.
Is there any way (not matter how) to retrieve the exclusive list of object
owned by a user ?
thanks,
Chris
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !Try this
SELECT o.name
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE u.name = 'username'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Chris V." <tophe_news@.hotmail.com> wrote in message
news:%23Q8GcImHFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Hi,
> My question may be stupid, but can;t figure out a way to do it "simply".
> I'm taking over the admin of some SQL instance and I want to clenu up the
> logins list. But before deleting anything, I need to know what objects are
> owned by the user I want to delete to not brak anything.
> Is there any way (not matter how) to retrieve the exclusive list of object
> owned by a user ?
> thanks,
> Chris
> --
> ________________________________________
______
> It's still better that if it would have been worst, isn't it ?
> C'est toujours mieux que si c'etait pire !
>
My question may be stupid, but can;t figure out a way to do it "simply".
I'm taking over the admin of some SQL instance and I want to clenu up the
logins list. But before deleting anything, I need to know what objects are
owned by the user I want to delete to not brak anything.
Is there any way (not matter how) to retrieve the exclusive list of object
owned by a user ?
thanks,
Chris
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !Try this
SELECT o.name
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE u.name = 'username'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Chris V." <tophe_news@.hotmail.com> wrote in message
news:%23Q8GcImHFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Hi,
> My question may be stupid, but can;t figure out a way to do it "simply".
> I'm taking over the admin of some SQL instance and I want to clenu up the
> logins list. But before deleting anything, I need to know what objects are
> owned by the user I want to delete to not brak anything.
> Is there any way (not matter how) to retrieve the exclusive list of object
> owned by a user ?
> thanks,
> Chris
> --
> ________________________________________
______
> It's still better that if it would have been worst, isn't it ?
> C'est toujours mieux que si c'etait pire !
>
Friday, March 23, 2012
Generating SQL scripts in VS2005
Hi,
I'm using Beta 2 and I've noticed a difference in behavior between VS2005 and VS2003 when generating SQL scripts from database objects in Server Explorer. In VS2003, scripting a table results in separate files (TAB, KEY etc) for the table, indexes and so on. With VS2005 I just get one file.
Is it possible to switch the behavior back to generating mutiple files? If not, why was the behavior changed?
Many thanks in advance!Hi Ian,
Did you ever get an answer to this?
(I've got the same problem!)
Thanks,
Steve|||You should try a different forum. This is an SSIS forum.
Generating scripts, tables, views, procedures, roles...
Hi...Database Publishing Wizard
|||Thomas, did you find a solution to your problem?
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.
Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
|||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
Generating scripts, tables, views, procedures, roles...
Hi...Database Publishing Wizard
|||Thomas, did you find a solution to your problem?
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.
Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
|||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
Generating scripts, tables, views, procedures, roles...
Hi...Database Publishing Wizard
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.
Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
|||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
Wednesday, March 21, 2012
Generating Script
Hi,
I want generated script file of all data base objects as well as i want to keep the generated script file in CVS which will reflect the changes.
Thanks in Advance
Sathik, Take a look at the Generate Script wizard in Management Studio. You can read more about it in Books Online: http://msdn2.microsoft.com/en-us/library/ms178078(SQL.90).aspx
Cheers,
Dan
Monday, March 19, 2012
Generating CREATE scripts for Oracle
Hello all,
My supervisor asked me to generate create scripts of one of our clients DB
objects. Apparently they use Oracle, so I need the scripts that SQL Server
2005 creates to be compatible with the SQL that Oracle uses. I know
absolutely nothing about Oracle-have no experience with it.
I went through the Generate SQL Server Scripts Wizard to create the scripts.
Scripts were created for 3 user-defined functions, 13 tables, and 20 views.
One thing that jumps to mind is SQL Server defaults to square brackets ([])
around object names. Will Oracle recognize square brackets? I think I read
somewhere once that some DB systems don't like square brackets, and quotes
("") should be used instead of square brackets. Is this something I need to
be concerned with?
Is there anything else that anyone can think of that I need to be concerned
with?
Thanks for any help anyone can provide,
Conan KellyOn Sep 14, 12:43 pm, "Conan Kelly"
<CTBarbarinNOS...@.msnNOSPAM.comNOSPAM> wrote:
> Hello all,
> My supervisor asked me to generate create scripts of one of our clients DB
> objects. Apparently they use Oracle, so I need the scripts that SQL Server
> 2005 creates to be compatible with the SQL that Oracle uses. I know
> absolutely nothing about Oracle-have no experience with it.
> I went through the Generate SQL Server Scripts Wizard to create the scripts.
> Scripts were created for 3 user-defined functions, 13 tables, and 20 views.
> One thing that jumps to mind is SQL Server defaults to square brackets ([])
> around object names. Will Oracle recognize square brackets? I think I read
> somewhere once that some DB systems don't like square brackets, and quotes
> ("") should be used instead of square brackets. Is this something I need to
> be concerned with?
> Is there anything else that anyone can think of that I need to be concerned
> with?
> Thanks for any help anyone can provide,
> Conan Kelly
I'd rather use Oracle Migration Workbench
My supervisor asked me to generate create scripts of one of our clients DB
objects. Apparently they use Oracle, so I need the scripts that SQL Server
2005 creates to be compatible with the SQL that Oracle uses. I know
absolutely nothing about Oracle-have no experience with it.
I went through the Generate SQL Server Scripts Wizard to create the scripts.
Scripts were created for 3 user-defined functions, 13 tables, and 20 views.
One thing that jumps to mind is SQL Server defaults to square brackets ([])
around object names. Will Oracle recognize square brackets? I think I read
somewhere once that some DB systems don't like square brackets, and quotes
("") should be used instead of square brackets. Is this something I need to
be concerned with?
Is there anything else that anyone can think of that I need to be concerned
with?
Thanks for any help anyone can provide,
Conan KellyOn Sep 14, 12:43 pm, "Conan Kelly"
<CTBarbarinNOS...@.msnNOSPAM.comNOSPAM> wrote:
> Hello all,
> My supervisor asked me to generate create scripts of one of our clients DB
> objects. Apparently they use Oracle, so I need the scripts that SQL Server
> 2005 creates to be compatible with the SQL that Oracle uses. I know
> absolutely nothing about Oracle-have no experience with it.
> I went through the Generate SQL Server Scripts Wizard to create the scripts.
> Scripts were created for 3 user-defined functions, 13 tables, and 20 views.
> One thing that jumps to mind is SQL Server defaults to square brackets ([])
> around object names. Will Oracle recognize square brackets? I think I read
> somewhere once that some DB systems don't like square brackets, and quotes
> ("") should be used instead of square brackets. Is this something I need to
> be concerned with?
> Is there anything else that anyone can think of that I need to be concerned
> with?
> Thanks for any help anyone can provide,
> Conan Kelly
I'd rather use Oracle Migration Workbench
Friday, March 9, 2012
Generate SQL Scripts
Hi all,
In Enterprise Manager, I could right click on a database and "generate
SQL Scripts" for hundreds of objects at once. In 2005, though, I've
only been able to generate scripts one at a time in Object Explorer,
and I haven't found any way to change the scripting option. Surely I'm
missing something?
- How do I generate scripts in 2005 (for many objects at a time, with
some custom options, as in SQL2000)?
- How do I change the Object Explorer right-click scripting options in
2005, when I'm scripting single objects?
Thanks in advance,
Myron> - How do I generate scripts in 2005 (for many objects at a time, with
> some custom options, as in SQL2000)?
Yes, seems like a step backward in this regard. Here are some excerpts from
a thread in MSDN's tools_general group that revolved around scripting all
the stored procedures (which could of course be replicated for several other
types of objects as well):
<I posted this>
Well, knowing that we can do this:
SELECT Object_Definition(Object_ID) FROM sys.procedures
The following might seem like a real brute force approach, but this will
generate the command line scripts necessary to do this (don't forget to use
the correct server and database, and change -E to -U/-P if using SQL auth):
select 'osql -S -E server -d database -Q"SELECT Object_Definition(Object_ID)
from sys.procedures
WHERE name='''+name+'''" > c:'+name+'.PRC' FROM sys.procedures
Take the output, paste into a .bat file, and run. (You may wish to modify
the result first, e.g. leave out specific procedures.) This should make the
generation of the files pretty painless, though they will not include any of
the typical SET options ON at the top and OFF at the end.
Of course, you could take this further, like writing these commands directly
to a batch file by executing xp_cmdshell directly, and then even executing
the file when you're done. But I was too lazy to deal with the nesting
quotes, which would quickly get far beyond anything I'd have the patience
for right now. :-) I also didn't want to get into the process of enabling
xp_cmdshell, which is disabled by default, and for good reason. If you have
enabled xp_cmdshell, feel free to experiment.
<Nilton posted this>
Hi,
I do not test in SQL Server 2005 but SCPTXFR.EXE is a very good command
line utility for it. It can be found in the C:\Program Files\Microsoft
SQL Server\MSSQL\Upgrade (SQL Server 2000) or x86\upgrade in SQL Server
2000 CD. It appears to be missing in SQL Server 2005 but...
More info..
http://www.norbtechnologies.com/pdf...>
%20Script.pdf|||Actually, in Microsoft SQL Server Management Studio, if you open your
database in Object Explorer, then go to tasks --> generate scripts and follo
w
the wizard through each step, you can script out any part of your database.
I
use this very often and it works very well.
HTH, Todd
"Aaron Bertrand [SQL Server MVP]" wrote:
> Yes, seems like a step backward in this regard. Here are some excerpts fr
om
> a thread in MSDN's tools_general group that revolved around scripting all
> the stored procedures (which could of course be replicated for several oth
er
> types of objects as well):
> <I posted this>
> Well, knowing that we can do this:
> SELECT Object_Definition(Object_ID) FROM sys.procedures
> The following might seem like a real brute force approach, but this will
> generate the command line scripts necessary to do this (don't forget to us
e
> the correct server and database, and change -E to -U/-P if using SQL auth)
:
> select 'osql -S -E server -d database -Q"SELECT Object_Definition(Object_I
D)
> from sys.procedures
> WHERE name='''+name+'''" > c:'+name+'.PRC' FROM sys.procedures
> Take the output, paste into a .bat file, and run. (You may wish to modify
> the result first, e.g. leave out specific procedures.) This should make t
he
> generation of the files pretty painless, though they will not include any
of
> the typical SET options ON at the top and OFF at the end.
> Of course, you could take this further, like writing these commands direct
ly
> to a batch file by executing xp_cmdshell directly, and then even executing
> the file when you're done. But I was too lazy to deal with the nesting
> quotes, which would quickly get far beyond anything I'd have the patience
> for right now. :-) I also didn't want to get into the process of enablin
g
> xp_cmdshell, which is disabled by default, and for good reason. If you ha
ve
> enabled xp_cmdshell, feel free to experiment.
>
> <Nilton posted this>
> Hi,
> I do not test in SQL Server 2005 but SCPTXFR.EXE is a very good command
> line utility for it. It can be found in the C:\Program Files\Microsoft
> SQL Server\MSSQL\Upgrade (SQL Server 2000) or x86\upgrade in SQL Server
> 2000 CD. It appears to be missing in SQL Server 2005 but...
> More info..
> http://www.norbtechnologies.com/pdf...on%20Script.pdf
>
>|||Another alternative is selecting a group of elements in the Summary window
and selecting Script > ... from the context menu. E.g. when the Tables node
is selected in the Object Explorer, the Summary window contains the list of
all user tables - select one or more to create scripts.
ML|||Thanks, all.
Anyone know how to get the scripting to add the DROP command or
permissions, like it did in 2000?|||Thanks, all.
Anyone know how to get the scripting to add the DROP command or
permissions, like it did in 2000?|||It's truly a step backwards. I'm writing code to do this now. It's only
about 30% complete but I'm getting tables, sps, views, triggers and
functions. Working on jobs right now. If you're interested I'll email you
the current code, unless you'd prefer to wait until its 100%.
It's a DLL and a front end console application with the following usage.
I'll add a GUI when I have time:
========================================
========================
DatabaseScriptor [-Out="{output folder}"][ -Preserve]
[ -Server="{name}"][ -Database="{name}"][ -User="" -Password=""]
[ -Include {objects}][ -Exclude {objects}][ -Encrypt][ -?]
[ -Force][ -NoDirs][-Quiet]
========================================
========================
This application is used to script objects in a named SQL Server database to
flat files.
SQL Server 2005's scripting functionality removed the ability to do this as
SQL Server 2000 did from the user interface.
========================================
========================
-? = Show this help text.
-D[atabase] = Optional. The name of the database whose object(s) will be
scripted. If not specified the user/login's default database
will be used.
-En[crypt] = Optional. If encryption is available, force encrypted
communication.
-Ex[clude] = Optional. The list of objects to exclude. If not specified no
objects will be exclueded.
-F[orce] = Optional. If true the output files will be overwritten
regardless of readonly state. Default is False.
-I[nclude] = Optional. The list of objects to script. If not specified all
objects will be included.
-N[oDirs] = Optional. If specified all files will be output to the specified
folder. Specialized folders will not be created.
-O[ut] = Optional. The folder to which to build the object tree. If not
specified all objects will be scripted to the console.
Duplicate file names will be overwritten.
-Pa[ssword] = Optional. The password for the SQL Server login.
-Q[uiet] = Optional. If specified no output will be sent to the console.
By default object names will be shown.
-Pr[eserve] = Optional. If true the output files will NOT be overwritten
regardless of readonly state. If false all files are overwritten.
Default is False.
-S[erver] = Optional. The name of the server that contains the database.
If not set, (local) will be assumed.
-U[ser] = Optional. The SQL Server login name to use for the scripting
operation. If not supplied the active user's account will be
used.
========================================
========================
In Enterprise Manager, I could right click on a database and "generate
SQL Scripts" for hundreds of objects at once. In 2005, though, I've
only been able to generate scripts one at a time in Object Explorer,
and I haven't found any way to change the scripting option. Surely I'm
missing something?
- How do I generate scripts in 2005 (for many objects at a time, with
some custom options, as in SQL2000)?
- How do I change the Object Explorer right-click scripting options in
2005, when I'm scripting single objects?
Thanks in advance,
Myron> - How do I generate scripts in 2005 (for many objects at a time, with
> some custom options, as in SQL2000)?
Yes, seems like a step backward in this regard. Here are some excerpts from
a thread in MSDN's tools_general group that revolved around scripting all
the stored procedures (which could of course be replicated for several other
types of objects as well):
<I posted this>
Well, knowing that we can do this:
SELECT Object_Definition(Object_ID) FROM sys.procedures
The following might seem like a real brute force approach, but this will
generate the command line scripts necessary to do this (don't forget to use
the correct server and database, and change -E to -U/-P if using SQL auth):
select 'osql -S -E server -d database -Q"SELECT Object_Definition(Object_ID)
from sys.procedures
WHERE name='''+name+'''" > c:'+name+'.PRC' FROM sys.procedures
Take the output, paste into a .bat file, and run. (You may wish to modify
the result first, e.g. leave out specific procedures.) This should make the
generation of the files pretty painless, though they will not include any of
the typical SET options ON at the top and OFF at the end.
Of course, you could take this further, like writing these commands directly
to a batch file by executing xp_cmdshell directly, and then even executing
the file when you're done. But I was too lazy to deal with the nesting
quotes, which would quickly get far beyond anything I'd have the patience
for right now. :-) I also didn't want to get into the process of enabling
xp_cmdshell, which is disabled by default, and for good reason. If you have
enabled xp_cmdshell, feel free to experiment.
<Nilton posted this>
Hi,
I do not test in SQL Server 2005 but SCPTXFR.EXE is a very good command
line utility for it. It can be found in the C:\Program Files\Microsoft
SQL Server\MSSQL\Upgrade (SQL Server 2000) or x86\upgrade in SQL Server
2000 CD. It appears to be missing in SQL Server 2005 but...
More info..
http://www.norbtechnologies.com/pdf...>
%20Script.pdf|||Actually, in Microsoft SQL Server Management Studio, if you open your
database in Object Explorer, then go to tasks --> generate scripts and follo
w
the wizard through each step, you can script out any part of your database.
I
use this very often and it works very well.
HTH, Todd
"Aaron Bertrand [SQL Server MVP]" wrote:
> Yes, seems like a step backward in this regard. Here are some excerpts fr
om
> a thread in MSDN's tools_general group that revolved around scripting all
> the stored procedures (which could of course be replicated for several oth
er
> types of objects as well):
> <I posted this>
> Well, knowing that we can do this:
> SELECT Object_Definition(Object_ID) FROM sys.procedures
> The following might seem like a real brute force approach, but this will
> generate the command line scripts necessary to do this (don't forget to us
e
> the correct server and database, and change -E to -U/-P if using SQL auth)
:
> select 'osql -S -E server -d database -Q"SELECT Object_Definition(Object_I
D)
> from sys.procedures
> WHERE name='''+name+'''" > c:'+name+'.PRC' FROM sys.procedures
> Take the output, paste into a .bat file, and run. (You may wish to modify
> the result first, e.g. leave out specific procedures.) This should make t
he
> generation of the files pretty painless, though they will not include any
of
> the typical SET options ON at the top and OFF at the end.
> Of course, you could take this further, like writing these commands direct
ly
> to a batch file by executing xp_cmdshell directly, and then even executing
> the file when you're done. But I was too lazy to deal with the nesting
> quotes, which would quickly get far beyond anything I'd have the patience
> for right now. :-) I also didn't want to get into the process of enablin
g
> xp_cmdshell, which is disabled by default, and for good reason. If you ha
ve
> enabled xp_cmdshell, feel free to experiment.
>
> <Nilton posted this>
> Hi,
> I do not test in SQL Server 2005 but SCPTXFR.EXE is a very good command
> line utility for it. It can be found in the C:\Program Files\Microsoft
> SQL Server\MSSQL\Upgrade (SQL Server 2000) or x86\upgrade in SQL Server
> 2000 CD. It appears to be missing in SQL Server 2005 but...
> More info..
> http://www.norbtechnologies.com/pdf...on%20Script.pdf
>
>|||Another alternative is selecting a group of elements in the Summary window
and selecting Script > ... from the context menu. E.g. when the Tables node
is selected in the Object Explorer, the Summary window contains the list of
all user tables - select one or more to create scripts.
ML|||Thanks, all.
Anyone know how to get the scripting to add the DROP command or
permissions, like it did in 2000?|||Thanks, all.
Anyone know how to get the scripting to add the DROP command or
permissions, like it did in 2000?|||It's truly a step backwards. I'm writing code to do this now. It's only
about 30% complete but I'm getting tables, sps, views, triggers and
functions. Working on jobs right now. If you're interested I'll email you
the current code, unless you'd prefer to wait until its 100%.
It's a DLL and a front end console application with the following usage.
I'll add a GUI when I have time:
========================================
========================
DatabaseScriptor [-Out="{output folder}"][ -Preserve]
[ -Server="{name}"][ -Database="{name}"][ -User="" -Password=""]
[ -Include {objects}][ -Exclude {objects}][ -Encrypt][ -?]
[ -Force][ -NoDirs][-Quiet]
========================================
========================
This application is used to script objects in a named SQL Server database to
flat files.
SQL Server 2005's scripting functionality removed the ability to do this as
SQL Server 2000 did from the user interface.
========================================
========================
-? = Show this help text.
-D[atabase] = Optional. The name of the database whose object(s) will be
scripted. If not specified the user/login's default database
will be used.
-En[crypt] = Optional. If encryption is available, force encrypted
communication.
-Ex[clude] = Optional. The list of objects to exclude. If not specified no
objects will be exclueded.
-F[orce] = Optional. If true the output files will be overwritten
regardless of readonly state. Default is False.
-I[nclude] = Optional. The list of objects to script. If not specified all
objects will be included.
-N[oDirs] = Optional. If specified all files will be output to the specified
folder. Specialized folders will not be created.
-O[ut] = Optional. The folder to which to build the object tree. If not
specified all objects will be scripted to the console.
Duplicate file names will be overwritten.
-Pa[ssword] = Optional. The password for the SQL Server login.
-Q[uiet] = Optional. If specified no output will be sent to the console.
By default object names will be shown.
-Pr[eserve] = Optional. If true the output files will NOT be overwritten
regardless of readonly state. If false all files are overwritten.
Default is False.
-S[erver] = Optional. The name of the server that contains the database.
If not set, (local) will be assumed.
-U[ser] = Optional. The SQL Server login name to use for the scripting
operation. If not supplied the active user's account will be
used.
========================================
========================
Generate SQL Script.
Hello, Folks
Problem in SQL EM , I can't generate sql script for selected tables with
depended objects.
Any ideas ?
Jonh Smith wrote:
> Hello, Folks
> Problem in SQL EM , I can't generate sql script for selected tables
> with depended objects.
> Any ideas ?
Could you let us know what's happening when you try to generate the DDL?
David G.
|||David,
It is only show me selected table(s) no depended.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...
> Jonh Smith wrote:
> Could you let us know what's happening when you try to generate the DDL?
> --
> David G.
>
|||I'm not sure how these objects you are extracting are related or how SQL
EM implements dependant object extraction. It may use sp_depends to
figure it out. If you run sp_depends on the objects you are extracting,
do you see the dependant objects listed?
David Gugick
Imceda Software
www.imceda.com
"Jonh Smith" <snapplefor@.hotmail.com> wrote in message
news:%23j00$GBTFHA.2124@.TK2MSFTNGP14.phx.gbl...
David,
It is only show me selected table(s) no depended.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...
> Jonh Smith wrote:
> Could you let us know what's happening when you try to generate the
> DDL?
> --
> David G.
>
|||In EM "Display all dependant" working fine,sp_depends working too
But what happen with Script Generator ? it don't generate for me dependant
objects.
Did you try your EM on this case ?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23IlOeNBTFHA.3620@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I'm not sure how these objects you are extracting are related or how SQL
> EM implements dependant object extraction. It may use sp_depends to figure
> it out. If you run sp_depends on the objects you are extracting, do you
> see the dependant objects listed?
> --
> David Gugick
> Imceda Software
> www.imceda.com
> "Jonh Smith" <snapplefor@.hotmail.com> wrote in message
> news:%23j00$GBTFHA.2124@.TK2MSFTNGP14.phx.gbl...
> David,
> It is only show me selected table(s) no depended.
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...
|||Jonh Smith wrote:
> In EM "Display all dependant" working fine,sp_depends working too
> But what happen with Script Generator ? it don't generate for me
> dependant objects.
> Did you try your EM on this case ?
>
I've used it in the past successfully. If I export the "titleauthor"
table from pubs and select the option to export dependant objects, it
works for me.
David Gugick
Imceda Software
www.imceda.com
|||David, you are right it is working for "pubs", but for different db is not.
What is a problem? What do you think?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O69APYCTFHA.3936@.TK2MSFTNGP15.phx.gbl...
> Jonh Smith wrote:
> I've used it in the past successfully. If I export the "titleauthor" table
> from pubs and select the option to export dependant objects, it works for
> me.
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Jonh Smith wrote:
> David, you are right it is working for "pubs", but for different db
> is not.
> What is a problem? What do you think?
>
I have no idea, unfortunately. Sorry.
David Gugick
Imceda Software
www.imceda.com
|||Thank you David for your response.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eq2tq4CTFHA.2304@.tk2msftngp13.phx.gbl...
> Jonh Smith wrote:
> I have no idea, unfortunately. Sorry.
> --
> David Gugick
> Imceda Software
> www.imceda.com
Problem in SQL EM , I can't generate sql script for selected tables with
depended objects.
Any ideas ?
Jonh Smith wrote:
> Hello, Folks
> Problem in SQL EM , I can't generate sql script for selected tables
> with depended objects.
> Any ideas ?
Could you let us know what's happening when you try to generate the DDL?
David G.
|||David,
It is only show me selected table(s) no depended.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...
> Jonh Smith wrote:
> Could you let us know what's happening when you try to generate the DDL?
> --
> David G.
>
|||I'm not sure how these objects you are extracting are related or how SQL
EM implements dependant object extraction. It may use sp_depends to
figure it out. If you run sp_depends on the objects you are extracting,
do you see the dependant objects listed?
David Gugick
Imceda Software
www.imceda.com
"Jonh Smith" <snapplefor@.hotmail.com> wrote in message
news:%23j00$GBTFHA.2124@.TK2MSFTNGP14.phx.gbl...
David,
It is only show me selected table(s) no depended.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...
> Jonh Smith wrote:
> Could you let us know what's happening when you try to generate the
> DDL?
> --
> David G.
>
|||In EM "Display all dependant" working fine,sp_depends working too
But what happen with Script Generator ? it don't generate for me dependant
objects.
Did you try your EM on this case ?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23IlOeNBTFHA.3620@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I'm not sure how these objects you are extracting are related or how SQL
> EM implements dependant object extraction. It may use sp_depends to figure
> it out. If you run sp_depends on the objects you are extracting, do you
> see the dependant objects listed?
> --
> David Gugick
> Imceda Software
> www.imceda.com
> "Jonh Smith" <snapplefor@.hotmail.com> wrote in message
> news:%23j00$GBTFHA.2124@.TK2MSFTNGP14.phx.gbl...
> David,
> It is only show me selected table(s) no depended.
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...
|||Jonh Smith wrote:
> In EM "Display all dependant" working fine,sp_depends working too
> But what happen with Script Generator ? it don't generate for me
> dependant objects.
> Did you try your EM on this case ?
>
I've used it in the past successfully. If I export the "titleauthor"
table from pubs and select the option to export dependant objects, it
works for me.
David Gugick
Imceda Software
www.imceda.com
|||David, you are right it is working for "pubs", but for different db is not.
What is a problem? What do you think?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O69APYCTFHA.3936@.TK2MSFTNGP15.phx.gbl...
> Jonh Smith wrote:
> I've used it in the past successfully. If I export the "titleauthor" table
> from pubs and select the option to export dependant objects, it works for
> me.
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Jonh Smith wrote:
> David, you are right it is working for "pubs", but for different db
> is not.
> What is a problem? What do you think?
>
I have no idea, unfortunately. Sorry.
David Gugick
Imceda Software
www.imceda.com
|||Thank you David for your response.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eq2tq4CTFHA.2304@.tk2msftngp13.phx.gbl...
> Jonh Smith wrote:
> I have no idea, unfortunately. Sorry.
> --
> David Gugick
> Imceda Software
> www.imceda.com
Generate SQL Script.
Hello, Folks
Problem in SQL EM , I can't generate sql script for selected tables with
depended objects.
Any ideas ?Jonh Smith wrote:
> Hello, Folks
> Problem in SQL EM , I can't generate sql script for selected tables
> with depended objects.
> Any ideas ?
Could you let us know what's happening when you try to generate the DDL?
David G.|||David,
It is only show me selected table(s) no depended.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:%23j85MDBTFHA.2556@.TK2MSFTNG
P12.phx.gbl...
> Jonh Smith wrote:
>
> Could you let us know what's happening when you try to generate the DDL?
>
> --
> David G.
>
>|||I'm not sure how these objects you are extracting are related or how SQL
EM implements dependant object extraction. It may use sp_depends to
figure it out. If you run sp_depends on the objects you are extracting,
do you see the dependant objects listed?
David Gugick
Imceda Software
www.imceda.com
"Jonh Smith" <snapplefor@.hotmail.com> wrote in message
news:%23j00$GBTFHA.2124@.TK2MSFTNGP14.phx.gbl...
David,
It is only show me selected table(s) no depended.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...
> Jonh Smith wrote:
> Could you let us know what's happening when you try to generate the
> DDL?
> --
> David G.
>|||In EM "Display all dependant" working fine,sp_depends working too
But what happen with Script Generator ? it don't generate for me dependant
objects.
Did you try your EM on this case ?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23IlOeNBTFHA.3620@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I'm not sure how these objects you are extracting are related or how SQL
> EM implements dependant object extraction. It may use sp_depends to figure
> it out. If you run sp_depends on the objects you are extracting, do you
> see the dependant objects listed?
> --
> David Gugick
> Imceda Software
> www.imceda.com
> "Jonh Smith" <snapplefor@.hotmail.com> wrote in message
> news:%23j00$GBTFHA.2124@.TK2MSFTNGP14.phx.gbl...
> David,
> It is only show me selected table(s) no depended.
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...|||Jonh Smith wrote:
> In EM "Display all dependant" working fine,sp_depends working too
> But what happen with Script Generator ? it don't generate for me
> dependant objects.
> Did you try your EM on this case ?
>
I've used it in the past successfully. If I export the "titleauthor"
table from pubs and select the option to export dependant objects, it
works for me.
David Gugick
Imceda Software
www.imceda.com|||David, you are right it is working for "pubs", but for different db is not.
What is a problem? What do you think?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O69APYCTFHA.3936@.TK2MSFTNGP15.phx.gbl...
> Jonh Smith wrote:
> I've used it in the past successfully. If I export the "titleauthor" table
> from pubs and select the option to export dependant objects, it works for
> me.
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Jonh Smith wrote:
> David, you are right it is working for "pubs", but for different db
> is not.
> What is a problem? What do you think?
>
I have no idea, unfortunately. Sorry.
David Gugick
Imceda Software
www.imceda.com|||Thank you David for your response.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eq2tq4CTFHA.2304@.tk2msftngp13.phx.gbl...
> Jonh Smith wrote:
> I have no idea, unfortunately. Sorry.
> --
> David Gugick
> Imceda Software
> www.imceda.com
Problem in SQL EM , I can't generate sql script for selected tables with
depended objects.
Any ideas ?Jonh Smith wrote:
> Hello, Folks
> Problem in SQL EM , I can't generate sql script for selected tables
> with depended objects.
> Any ideas ?
Could you let us know what's happening when you try to generate the DDL?
David G.|||David,
It is only show me selected table(s) no depended.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:%23j85MDBTFHA.2556@.TK2MSFTNG
P12.phx.gbl...
> Jonh Smith wrote:
>
> Could you let us know what's happening when you try to generate the DDL?
>
> --
> David G.
>
>|||I'm not sure how these objects you are extracting are related or how SQL
EM implements dependant object extraction. It may use sp_depends to
figure it out. If you run sp_depends on the objects you are extracting,
do you see the dependant objects listed?
David Gugick
Imceda Software
www.imceda.com
"Jonh Smith" <snapplefor@.hotmail.com> wrote in message
news:%23j00$GBTFHA.2124@.TK2MSFTNGP14.phx.gbl...
David,
It is only show me selected table(s) no depended.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...
> Jonh Smith wrote:
> Could you let us know what's happening when you try to generate the
> DDL?
> --
> David G.
>|||In EM "Display all dependant" working fine,sp_depends working too
But what happen with Script Generator ? it don't generate for me dependant
objects.
Did you try your EM on this case ?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23IlOeNBTFHA.3620@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I'm not sure how these objects you are extracting are related or how SQL
> EM implements dependant object extraction. It may use sp_depends to figure
> it out. If you run sp_depends on the objects you are extracting, do you
> see the dependant objects listed?
> --
> David Gugick
> Imceda Software
> www.imceda.com
> "Jonh Smith" <snapplefor@.hotmail.com> wrote in message
> news:%23j00$GBTFHA.2124@.TK2MSFTNGP14.phx.gbl...
> David,
> It is only show me selected table(s) no depended.
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...|||Jonh Smith wrote:
> In EM "Display all dependant" working fine,sp_depends working too
> But what happen with Script Generator ? it don't generate for me
> dependant objects.
> Did you try your EM on this case ?
>
I've used it in the past successfully. If I export the "titleauthor"
table from pubs and select the option to export dependant objects, it
works for me.
David Gugick
Imceda Software
www.imceda.com|||David, you are right it is working for "pubs", but for different db is not.
What is a problem? What do you think?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O69APYCTFHA.3936@.TK2MSFTNGP15.phx.gbl...
> Jonh Smith wrote:
> I've used it in the past successfully. If I export the "titleauthor" table
> from pubs and select the option to export dependant objects, it works for
> me.
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Jonh Smith wrote:
> David, you are right it is working for "pubs", but for different db
> is not.
> What is a problem? What do you think?
>
I have no idea, unfortunately. Sorry.
David Gugick
Imceda Software
www.imceda.com|||Thank you David for your response.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eq2tq4CTFHA.2304@.tk2msftngp13.phx.gbl...
> Jonh Smith wrote:
> I have no idea, unfortunately. Sorry.
> --
> David Gugick
> Imceda Software
> www.imceda.com
Generate SQL Script.
Hello, Folks
Problem in SQL EM , I can't generate sql script for selected tables with
depended objects.
Any ideas ?Jonh Smith wrote:
> Hello, Folks
> Problem in SQL EM , I can't generate sql script for selected tables
> with depended objects.
> Any ideas ?
Could you let us know what's happening when you try to generate the DDL?
--
David G.|||This is a multi-part message in MIME format.
--=_NextPart_000_0072_01C54BF1.2D1CA260
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
David,
It is only show me selected table(s) no depended.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message =news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...
> Jonh Smith wrote:
>> Hello, Folks
>> Problem in SQL EM , I can't generate sql script for selected tables
>> with depended objects.
>> Any ideas ?
> > Could you let us know what's happening when you try to generate the =DDL?
> > -- > David G.
> >
--=_NextPart_000_0072_01C54BF1.2D1CA260
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
David,
It is only show me selected table(s) =no depended.
"David Gugick" wrote in message news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...> =Jonh Smith wrote:> Hello, Folks> Problem in SQL EM , I can't =generate sql script for selected tables> with depended =objects.> Any ideas ?> > Could you let us know what's happening when =you try to generate the DDL?> > -- > David G.> >
--=_NextPart_000_0072_01C54BF1.2D1CA260--|||I'm not sure how these objects you are extracting are related or how SQL
EM implements dependant object extraction. It may use sp_depends to
figure it out. If you run sp_depends on the objects you are extracting,
do you see the dependant objects listed?
--
David Gugick
Imceda Software
www.imceda.com
"Jonh Smith" <snapplefor@.hotmail.com> wrote in message
news:%23j00$GBTFHA.2124@.TK2MSFTNGP14.phx.gbl...
David,
It is only show me selected table(s) no depended.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...
> Jonh Smith wrote:
>> Hello, Folks
>> Problem in SQL EM , I can't generate sql script for selected tables
>> with depended objects.
>> Any ideas ?
> Could you let us know what's happening when you try to generate the
> DDL?
> --
> David G.
>|||In EM "Display all dependant" working fine,sp_depends working too
But what happen with Script Generator ? it don't generate for me dependant
objects.
Did you try your EM on this case ?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23IlOeNBTFHA.3620@.TK2MSFTNGP09.phx.gbl...
> I'm not sure how these objects you are extracting are related or how SQL
> EM implements dependant object extraction. It may use sp_depends to figure
> it out. If you run sp_depends on the objects you are extracting, do you
> see the dependant objects listed?
> --
> David Gugick
> Imceda Software
> www.imceda.com
> "Jonh Smith" <snapplefor@.hotmail.com> wrote in message
> news:%23j00$GBTFHA.2124@.TK2MSFTNGP14.phx.gbl...
> David,
> It is only show me selected table(s) no depended.
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...
>> Jonh Smith wrote:
>> Hello, Folks
>> Problem in SQL EM , I can't generate sql script for selected tables
>> with depended objects.
>> Any ideas ?
>> Could you let us know what's happening when you try to generate the DDL?
>> --
>> David G.
>>|||Jonh Smith wrote:
> In EM "Display all dependant" working fine,sp_depends working too
> But what happen with Script Generator ? it don't generate for me
> dependant objects.
> Did you try your EM on this case ?
>
I've used it in the past successfully. If I export the "titleauthor"
table from pubs and select the option to export dependant objects, it
works for me.
--
David Gugick
Imceda Software
www.imceda.com|||David, you are right it is working for "pubs", but for different db is not.
What is a problem? What do you think?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O69APYCTFHA.3936@.TK2MSFTNGP15.phx.gbl...
> Jonh Smith wrote:
>> In EM "Display all dependant" working fine,sp_depends working too
>> But what happen with Script Generator ? it don't generate for me
>> dependant objects.
>> Did you try your EM on this case ?
> I've used it in the past successfully. If I export the "titleauthor" table
> from pubs and select the option to export dependant objects, it works for
> me.
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Jonh Smith wrote:
> David, you are right it is working for "pubs", but for different db
> is not.
> What is a problem? What do you think?
>
I have no idea, unfortunately. Sorry.
--
David Gugick
Imceda Software
www.imceda.com|||Thank you David for your response.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eq2tq4CTFHA.2304@.tk2msftngp13.phx.gbl...
> Jonh Smith wrote:
>> David, you are right it is working for "pubs", but for different db
>> is not. What is a problem? What do you think?
> I have no idea, unfortunately. Sorry.
> --
> David Gugick
> Imceda Software
> www.imceda.com
Problem in SQL EM , I can't generate sql script for selected tables with
depended objects.
Any ideas ?Jonh Smith wrote:
> Hello, Folks
> Problem in SQL EM , I can't generate sql script for selected tables
> with depended objects.
> Any ideas ?
Could you let us know what's happening when you try to generate the DDL?
--
David G.|||This is a multi-part message in MIME format.
--=_NextPart_000_0072_01C54BF1.2D1CA260
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
David,
It is only show me selected table(s) no depended.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message =news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...
> Jonh Smith wrote:
>> Hello, Folks
>> Problem in SQL EM , I can't generate sql script for selected tables
>> with depended objects.
>> Any ideas ?
> > Could you let us know what's happening when you try to generate the =DDL?
> > -- > David G.
> >
--=_NextPart_000_0072_01C54BF1.2D1CA260
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
David,
It is only show me selected table(s) =no depended.
"David Gugick"
--=_NextPart_000_0072_01C54BF1.2D1CA260--|||I'm not sure how these objects you are extracting are related or how SQL
EM implements dependant object extraction. It may use sp_depends to
figure it out. If you run sp_depends on the objects you are extracting,
do you see the dependant objects listed?
--
David Gugick
Imceda Software
www.imceda.com
"Jonh Smith" <snapplefor@.hotmail.com> wrote in message
news:%23j00$GBTFHA.2124@.TK2MSFTNGP14.phx.gbl...
David,
It is only show me selected table(s) no depended.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...
> Jonh Smith wrote:
>> Hello, Folks
>> Problem in SQL EM , I can't generate sql script for selected tables
>> with depended objects.
>> Any ideas ?
> Could you let us know what's happening when you try to generate the
> DDL?
> --
> David G.
>|||In EM "Display all dependant" working fine,sp_depends working too
But what happen with Script Generator ? it don't generate for me dependant
objects.
Did you try your EM on this case ?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23IlOeNBTFHA.3620@.TK2MSFTNGP09.phx.gbl...
> I'm not sure how these objects you are extracting are related or how SQL
> EM implements dependant object extraction. It may use sp_depends to figure
> it out. If you run sp_depends on the objects you are extracting, do you
> see the dependant objects listed?
> --
> David Gugick
> Imceda Software
> www.imceda.com
> "Jonh Smith" <snapplefor@.hotmail.com> wrote in message
> news:%23j00$GBTFHA.2124@.TK2MSFTNGP14.phx.gbl...
> David,
> It is only show me selected table(s) no depended.
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...
>> Jonh Smith wrote:
>> Hello, Folks
>> Problem in SQL EM , I can't generate sql script for selected tables
>> with depended objects.
>> Any ideas ?
>> Could you let us know what's happening when you try to generate the DDL?
>> --
>> David G.
>>|||Jonh Smith wrote:
> In EM "Display all dependant" working fine,sp_depends working too
> But what happen with Script Generator ? it don't generate for me
> dependant objects.
> Did you try your EM on this case ?
>
I've used it in the past successfully. If I export the "titleauthor"
table from pubs and select the option to export dependant objects, it
works for me.
--
David Gugick
Imceda Software
www.imceda.com|||David, you are right it is working for "pubs", but for different db is not.
What is a problem? What do you think?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O69APYCTFHA.3936@.TK2MSFTNGP15.phx.gbl...
> Jonh Smith wrote:
>> In EM "Display all dependant" working fine,sp_depends working too
>> But what happen with Script Generator ? it don't generate for me
>> dependant objects.
>> Did you try your EM on this case ?
> I've used it in the past successfully. If I export the "titleauthor" table
> from pubs and select the option to export dependant objects, it works for
> me.
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Jonh Smith wrote:
> David, you are right it is working for "pubs", but for different db
> is not.
> What is a problem? What do you think?
>
I have no idea, unfortunately. Sorry.
--
David Gugick
Imceda Software
www.imceda.com|||Thank you David for your response.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eq2tq4CTFHA.2304@.tk2msftngp13.phx.gbl...
> Jonh Smith wrote:
>> David, you are right it is working for "pubs", but for different db
>> is not. What is a problem? What do you think?
> I have no idea, unfortunately. Sorry.
> --
> David Gugick
> Imceda Software
> www.imceda.com
Generate SQL Script for Stored Procedures
I want to script some stored procedures before I drop
them. When I right click on the master database, and
select generate sql script, I see 2 objects for the master
database, the helpbox procedures.
If I right click on the stored procedure itself, the
generate sql script is greyed out.
would appreciate help on this. Thanks.Hi,
Master database contains only the system stored procedures and those will
not be shown in "Generate SQL Script". Please try with your user database
and try generating the script.
Thanks
Hari
MCDBA
"Barb" <anonymous@.discussions.microsoft.com> wrote in message
news:12f2e01c41203$270271d0$a401280a@.phx
.gbl...
> I want to script some stored procedures before I drop
> them. When I right click on the master database, and
> select generate sql script, I see 2 objects for the master
> database, the helpbox procedures.
> If I right click on the stored procedure itself, the
> generate sql script is greyed out.
> would appreciate help on this. Thanks.|||As Hari pointed out, system stored procedures won't show up
in the Generate SQL Script dialog from Enterprise Manager.
If you are trying to script out system stored procedures,
you can script these individually using the object browser
in Query Analyzer. If you right click on the stored
procedure in the object browser, you get scripting options.
You can script the stored procedure to a file by selecting
Script Object to File as Create.
-Sue
On Wed, 24 Mar 2004 16:50:22 -0800, "Barb"
<anonymous@.discussions.microsoft.com> wrote:
>I want to script some stored procedures before I drop
>them. When I right click on the master database, and
>select generate sql script, I see 2 objects for the master
>database, the helpbox procedures.
>If I right click on the stored procedure itself, the
>generate sql script is greyed out.
>would appreciate help on this. Thanks.|||Thank You.
>--Original Message--
>As Hari pointed out, system stored procedures won't show
up
>in the Generate SQL Script dialog from Enterprise Manager.
>If you are trying to script out system stored procedures,
>you can script these individually using the object browser
>in Query Analyzer. If you right click on the stored
>procedure in the object browser, you get scripting
options.
>You can script the stored procedure to a file by selecting
>Script Object to File as Create.
>-Sue
>On Wed, 24 Mar 2004 16:50:22 -0800, "Barb"
><anonymous@.discussions.microsoft.com> wrote:
>
master
>.
>|||Thank You.
>--Original Message--
>Hi,
>Master database contains only the system stored
procedures and those will
>not be shown in "Generate SQL Script". Please try with
your user database
>and try generating the script.
>Thanks
>Hari
>MCDBA
>"Barb" <anonymous@.discussions.microsoft.com> wrote in
message
> news:12f2e01c41203$270271d0$a401280a@.phx
.gbl...
master
>
>.
>
them. When I right click on the master database, and
select generate sql script, I see 2 objects for the master
database, the helpbox procedures.
If I right click on the stored procedure itself, the
generate sql script is greyed out.
would appreciate help on this. Thanks.Hi,
Master database contains only the system stored procedures and those will
not be shown in "Generate SQL Script". Please try with your user database
and try generating the script.
Thanks
Hari
MCDBA
"Barb" <anonymous@.discussions.microsoft.com> wrote in message
news:12f2e01c41203$270271d0$a401280a@.phx
.gbl...
> I want to script some stored procedures before I drop
> them. When I right click on the master database, and
> select generate sql script, I see 2 objects for the master
> database, the helpbox procedures.
> If I right click on the stored procedure itself, the
> generate sql script is greyed out.
> would appreciate help on this. Thanks.|||As Hari pointed out, system stored procedures won't show up
in the Generate SQL Script dialog from Enterprise Manager.
If you are trying to script out system stored procedures,
you can script these individually using the object browser
in Query Analyzer. If you right click on the stored
procedure in the object browser, you get scripting options.
You can script the stored procedure to a file by selecting
Script Object to File as Create.
-Sue
On Wed, 24 Mar 2004 16:50:22 -0800, "Barb"
<anonymous@.discussions.microsoft.com> wrote:
>I want to script some stored procedures before I drop
>them. When I right click on the master database, and
>select generate sql script, I see 2 objects for the master
>database, the helpbox procedures.
>If I right click on the stored procedure itself, the
>generate sql script is greyed out.
>would appreciate help on this. Thanks.|||Thank You.
>--Original Message--
>As Hari pointed out, system stored procedures won't show
up
>in the Generate SQL Script dialog from Enterprise Manager.
>If you are trying to script out system stored procedures,
>you can script these individually using the object browser
>in Query Analyzer. If you right click on the stored
>procedure in the object browser, you get scripting
options.
>You can script the stored procedure to a file by selecting
>Script Object to File as Create.
>-Sue
>On Wed, 24 Mar 2004 16:50:22 -0800, "Barb"
><anonymous@.discussions.microsoft.com> wrote:
>
master
>.
>|||Thank You.
>--Original Message--
>Hi,
>Master database contains only the system stored
procedures and those will
>not be shown in "Generate SQL Script". Please try with
your user database
>and try generating the script.
>Thanks
>Hari
>MCDBA
>"Barb" <anonymous@.discussions.microsoft.com> wrote in
message
> news:12f2e01c41203$270271d0$a401280a@.phx
.gbl...
master
>
>.
>
Wednesday, March 7, 2012
Generate script of ALL database objects
I need to be able to programatically generate the CREATE scripts for all objects for a given database. Basically, I need the same results as if I generated the script using SSMS and choosing to script all objects in the selected database.
I'm hoping I could do this using the Scripter w/in the SMO Utility Classes. Can anyone help me out?
Thanks in advance!Hi,
use the transfer method of the scripter:
http://blogs.msdn.com/mwories/articles/smosample_transfer.aspx
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||Thanks Jens. Just what I was looking for. :-)|||Take a look at this free tool -- it scripts out all database objects in a manner which mimics Visual Studio for Database Professionals [the "Data Dude"]. That is, it creates a separate file for each object in the database in an organized file tree.
http://sourceforge.net/projects/script-sql-db
Richard|||Would anyone happen to know how I could get the end of a batch of Transact-SQL statements; i.e "GO" statements to show up in generated script?
Thanks.|||As you get a stringCollection using the
System.Collections.Specialized.StringCollection s = t.ScriptTransfer(); // Or use ScriptTransfer() if you need to capture the script (without data)
You can simply modify the script on your own.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Well, yes, I see your point, but then wouldn't I have to write a decent amount of conditional code to to determine if I need a "GO" line or not? Assuming I would not want a "GO" statement on each line as I'd like to batch some of the operations.I noticed that the scripter object generates code with the embedded "GO" statements, does the transfer object not provide this capability?
Thanks.|||There is a property in the Transfer object named 'FileName'. When you supply this property with a file path the generated script is output to a text file and the 'GO' statements are in the text file.
Generate script of ALL database objects
I need to be able to programatically generate the CREATE scripts for all objects for a given database. Basically, I need the same results as if I generated the script using SSMS and choosing to script all objects in the selected database.
I'm hoping I could do this using the Scripter w/in the SMO Utility Classes. Can anyone help me out?
Thanks in advance!Hi,
use the transfer method of the scripter:
http://blogs.msdn.com/mwories/articles/smosample_transfer.aspx
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||Thanks Jens. Just what I was looking for. :-)|||Take a look at this free tool -- it scripts out all database objects in a manner which mimics Visual Studio for Database Professionals [the "Data Dude"]. That is, it creates a separate file for each object in the database in an organized file tree.
http://sourceforge.net/projects/script-sql-db
Richard|||Would anyone happen to know how I could get the end of a batch of Transact-SQL statements; i.e "GO" statements to show up in generated script?
Thanks.|||As you get a stringCollection using the
System.Collections.Specialized.StringCollection s = t.ScriptTransfer(); // Or use ScriptTransfer() if you need to capture the script (without data)
You can simply modify the script on your own.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Well, yes, I see your point, but then wouldn't I have to write a decent amount of conditional code to to determine if I need a "GO" line or not? Assuming I would not want a "GO" statement on each line as I'd like to batch some of the operations.I noticed that the scripter object generates code with the embedded "GO" statements, does the transfer object not provide this capability?
Thanks.|||There is a property in the Transfer object named 'FileName'. When you supply this property with a file path the generated script is output to a text file and the 'GO' statements are in the text file.
Sunday, February 26, 2012
Generate relational schema - old tables are always deleted and not recreated
I want to use the top down modelling features and generate tables from the OLAP objects by generating relational schema.
The problem:
First time using the "generate relational schema" - the tables are created.
Next time using this feature: new tables are generated, but all tables created in the past are lost. There is a warning, the schema can't be created, because there are user defined tables.
I found a way to reset this status, if I open the code for a dimension and delete the section from <Source to /Source>:
<Source xsi:type="DataSourceViewBinding" dwd:design-time-name="51a3fe82-65d2-402e-a846-d2b9689b177c">
<DataSourceViewID>LR OLAP</DataSourceViewID>
</Source>
After this I can recreate the tables. But there must be another way. I can't delete the source information from all object before each using of the "generate relational schema".
In the past, when I was evaluating the SSAS 2005, I had no such problems. What happens? Why are the tables deleted each time, if I generate relational schema?
The way you are trying to use Analysis Services is not exactly how it was envisioned originally.
The logic for which tables needs to be created is pretty simple. If object doesnt have bindings, the schema generation wizard will create a table in relational database supporting it.
Now, creating relational database schema is considered one time operation. Analysis Services is not exactly application to use for generating relational schemas. You go through the wizard, it creates all the tables for you and then you expected to keep and maintain set of tables created.
HTH
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)