How to get the value of script of various SQLDMO(version 8.0) objects

Hello,
I am working on a project where I want to access and modify some objects of Microsoft SQL 2000 database.
Would someone help me how to do this? I need following informations;
- Script of the checks of a table
- Script of the Indexes of a table
- Script of Key object(Primary or Forign key)
- Script of Permissions of View and Stored procedure

The following code returns no result;

MyTabelClass mytabelClass = new MyTabelClass();

mytabelClass.Naam = table.Name;

mytabelClass.ChecksScript = table.Script(SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_Checks,string.Empty,string.Empty,SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default);

mytabelClass.IndexesScript = table.Script(SQLDMO_SCRIPT_TYPE.SQLDMOScript_Indexes,string.Empty,string.Empty,SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default);

Thanks in advance.
With regards,

Your script is incorrect. Instead of accessing an existing object, you create a new one :

MyTabelClass mytabelClass = new MyTabelClass(); create a new instance.

See this sample:

Dim svr As New SQLServer
svr.Name = "."
svr.LoginSecure = True
svr.Connect

Dim t As Table

Set t = svr.Databases("pubs").Tables("authors")

MsgBox t.Script(SQLDMOScript_DRI_Checks, "", "", SQLDMOScript2_Default)
MsgBox t.Script(SQLDMOScript_Indexes, "", "", SQLDMOScript2_Default)

Groeten/Greetings

How to get the value of script of various SQLDMO(version 8.0) objects

Hello,
I am working on a project where I want to access and modify some objects of Microsoft SQL 2000 database.
Would someone help me how to do this? I need following informations;
- Script of the checks of a table
- Script of the Indexes of a table
- Script of Key object(Primary or Forign key)
- Script of Permissions of View and Stored procedure

The following code returns no result;

MyTabelClass mytabelClass = new MyTabelClass();

mytabelClass.Naam = table.Name;

mytabelClass.ChecksScript = table.Script(SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_Checks,string.Empty,string.Empty,SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default);

mytabelClass.IndexesScript = table.Script(SQLDMO_SCRIPT_TYPE.SQLDMOScript_Indexes,string.Empty,string.Empty,SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default);

Thanks in advance.
With regards,

Your script is incorrect. Instead of accessing an existing object, you create a new one :

MyTabelClass mytabelClass = new MyTabelClass(); create a new instance.

See this sample:

Dim svr As New SQLServer
svr.Name = "."
svr.LoginSecure = True
svr.Connect

Dim t As Table

Set t = svr.Databases("pubs").Tables("authors")

MsgBox t.Script(SQLDMOScript_DRI_Checks, "", "", SQLDMOScript2_Default)
MsgBox t.Script(SQLDMOScript_Indexes, "", "", SQLDMOScript2_Default)

Groeten/Greetings

How to get the Script for an object using query Analyser?

Hi,

I wanted to know how to get the script for an object using query analyser is there a system procedure to get the script. how does the enterprise manager generate the script?

Check out sp_helpText in BOL.

Cheers,
Loonysan

|||

sp_helptext is used for procedure,view,trigger etc

what if i want to get the scrip of a table? or job like what we get in the enterprise manage using the GENERATE sCRIPT option

|||

sp_help describes database objects. It might not be in the exact format you need it, but you should be able to manipulate the stored procedure in the way you need it. I also did a query like the following and this gave me information like column name, datatype and length of a table. There are other columns in the syscolumns table that specifiy collation and if it can contain a null (isnullable field in syscolumns).

select c.[name],t.[name],c.length
from sysobjects o
join syscolumns c
on o.id = c.id
join systypes t
on c.xtype = t.xtype
where o.[name] = 'nissannow_orders'

Results:

CreateDate datetime 8
OrderDate datetime 8
ExportSubmissionDate datetime 8
ModifiedDate datetime 8
OrderID int 4
CustomerID int 4
DealerIDDealerVisited int 4
CreatedBy varchar 50
ShipPrimaryPhone varchar 20
ShipEmailAddress varchar 50
ModifiedBy varchar 50
ShipCity varchar 50
ShipStateProvince varchar 50
ShipZip varchar 20
ShipFirstName varchar 100
ShipLastName varchar 100
ShipAddress1 varchar 100

Let me know if you need any more assistance with this.

|||There is no easy way to do this in TSQL. You need to either write a SP that does the work using the metadata in the system tables or use the built-in client API like DMO/SMO. You can write VB scripts for example that uses DMO to generate scripts or DTS/SSIS tasks.

How to get the name of the error column from the errorcolumn output

Hi,

Iam redirecting the error output of a OLEDB destination component to a script component. My aim is to create a HTML report having the information about the bad records, the error occuring in the rows and the column name that fails. The error output provided two new columns i.e the errorcode and errorcolumn , the errorcolumn value for a bad record gives the linage id for the column, is there a way to derieve the name of the column by using the lineage id?

Regard,

pritesh

Possible, but not trivial. MS seem to consider this a design-time lookup, so you could use something that queries the package structure to get the info. Simon has written a transform that does this as well, note it needs both outputs to be able to get all the metadata. http://sqlblogcasts.com/files/3/transforms/entry2.aspx|||Thanks for the info , i will try to implement it. It sounds useful !|||

Anonymous wrote:

Thanks for the info , i will try to implement it. It sounds useful !

And if you want to continue posting here, please change your display name from Anonymous to something else. Microsoft staff will be cleaning up those accounts with display names of Anonymous.

how to get sqlcmd return values

I'm having a hard time getting a non-zero return value from sqlcmd when a
error in the sql script it is executing occurs.
I've tried setting the -V parm to 10 and -m to 10 (although don't thing -m
is relevant) but regardless ERRORLEVEL is always 0
Here is a sample of the sqlcmd and the os syntax I'm running from within a
sql agent job step:
sqlcmd -U user -P pass -S server -V 10 -h-1 -i "C:\Admin\bcp table space
input file.sql"
echo %ERRORLEVEL%
or
set %ERRORLEVEL% = sqlcmd -U user -P pass -S server -V 10 -h-1 -i
"C:\Admin\bcp table space input file.sql"
also, I've tried different iterations with and without spaces between -V and
10
any help or suggestions is greatly appreciated.What error occurs on the script you run? I just tried with RAISERROR and it
work find. Here's the
bat file:
sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
ECHO %ERRORLEVEL%
pause
And here's the contents of a.sql:
RAISERROR('Ouch', 15, 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paul" <paul@.discussions.microsoft.com> wrote in message
news:5CD9F397-04BD-465F-A818-2E77C30D43BC@.microsoft.com...
> I'm having a hard time getting a non-zero return value from sqlcmd when a
> error in the sql script it is executing occurs.
> I've tried setting the -V parm to 10 and -m to 10 (although don't thing -m
> is relevant) but regardless ERRORLEVEL is always 0
> Here is a sample of the sqlcmd and the os syntax I'm running from within a
> sql agent job step:
> sqlcmd -U user -P pass -S server -V 10 -h-1 -i "C:\Admin\bcp table space
> input file.sql"
> echo %ERRORLEVEL%
> or
> set %ERRORLEVEL% = sqlcmd -U user -P pass -S server -V 10 -h-1 -i
> "C:\Admin\bcp table space input file.sql"
> also, I've tried different iterations with and without spaces between -V a
nd
> 10
>
> any help or suggestions is greatly appreciated.
>|||Here is the error message:
Msg 945, Level 14, State 2, Server servername, Line 1
Database 'databasename' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for details.
Basically I iterate through different databases and select from some sys
tables. The problem here is one particular db is either in a loading state
or in a suspect state and therefore causes the error. Nonetheless, resolvin
g
that issue is not hard and not as important as just catching the error itsel
f.
Thanks
"Tibor Karaszi" wrote:

> What error occurs on the script you run? I just tried with RAISERROR and i
t work find. Here's the
> bat file:
> sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
> ECHO %ERRORLEVEL%
> pause
> And here's the contents of a.sql:
> RAISERROR('Ouch', 15, 1)
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:5CD9F397-04BD-465F-A818-2E77C30D43BC@.microsoft.com...
>|||Try the "-b" command line option.
"paul" <paul@.discussions.microsoft.com> wrote in message
news:5CD9F397-04BD-465F-A818-2E77C30D43BC@.microsoft.com...
> I'm having a hard time getting a non-zero return value from sqlcmd when a
> error in the sql script it is executing occurs.
> I've tried setting the -V parm to 10 and -m to 10 (although don't thing -m
> is relevant) but regardless ERRORLEVEL is always 0
> Here is a sample of the sqlcmd and the os syntax I'm running from within a
> sql agent job step:
> sqlcmd -U user -P pass -S server -V 10 -h-1 -i "C:\Admin\bcp table space
> input file.sql"
> echo %ERRORLEVEL%
> or
> set %ERRORLEVEL% = sqlcmd -U user -P pass -S server -V 10 -h-1 -i
> "C:\Admin\bcp table space input file.sql"
> also, I've tried different iterations with and without spaces between -V
> and
> 10
>
> any help or suggestions is greatly appreciated.
>|||Tried this but didn't work. Is my dos look right?
!!sqlcmd -U username -P password -S servername -b -V 10 -h-1 -i
"C:\Admin\bcp table space input file.sql"
!!echo %ERRORLEVEL%
with or without -V doesn't work?
"Mike C#" wrote:

> Try the "-b" command line option.
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:5CD9F397-04BD-465F-A818-2E77C30D43BC@.microsoft.com...
>
>|||It work just fine for me with such an error as well. Here's what I did:
CREATE DATABASE x
ALTER DATABASE x SET OFFLINE
And here's what I have in the bat file:
sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
ECHO %ERRORLEVEL%
pause
And what is in a.sql:
SELECT 'Hello'
SELECT * FROM x..sysobjects
SELECT 'Hello again'
And below is output from executing the bat file:
C:\>sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
Msg 942, Level 14, State 4, Server TIBWORK\RTM, Line 2
Database 'x' cannot be opened because it is offline.
C:\>ECHO 14
14
C:\>pause
Press any key to continue . . .
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paul" <paul@.discussions.microsoft.com> wrote in message
news:7B3754E7-C857-4BE3-9DFD-14BED25FD33B@.microsoft.com...
> Here is the error message:
> Msg 945, Level 14, State 2, Server servername, Line 1
> Database 'databasename' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for detail
s.
>
> Basically I iterate through different databases and select from some sys
> tables. The problem here is one particular db is either in a loading stat
e
> or in a suspect state and therefore causes the error. Nonetheless, resolv
ing
> that issue is not hard and not as important as just catching the error its
elf.
> Thanks
>
> "Tibor Karaszi" wrote:
>|||Tibor,
Thanks for following through. Can you try one more thing that I'm wondering
may be having an effect.
Change you select sql to be a dynamically executed string.
Like this:
declare @.sql varchar(255)
set @.sql = 'select * from x..sysobjects'
select 'Hello'
exec(@.sql)
select 'Hello again'
If that works on your machine, I would say it is something local to mine,
else I'm wondering if exec() behaves the way I'm expecting it to.
Thanks again.
"Tibor Karaszi" wrote:

> It work just fine for me with such an error as well. Here's what I did:
> CREATE DATABASE x
> ALTER DATABASE x SET OFFLINE
> And here's what I have in the bat file:
> sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
> ECHO %ERRORLEVEL%
> pause
> And what is in a.sql:
> SELECT 'Hello'
> SELECT * FROM x..sysobjects
> SELECT 'Hello again'
>
> And below is output from executing the bat file:
> C:\>sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
> Msg 942, Level 14, State 4, Server TIBWORK\RTM, Line 2
> Database 'x' cannot be opened because it is offline.
> C:\>ECHO 14
> 14
> C:\>pause
> Press any key to continue . . .
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:7B3754E7-C857-4BE3-9DFD-14BED25FD33B@.microsoft.com...
>|||Same result. I still managed to catch the error with errorlevel.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paul" <paul@.discussions.microsoft.com> wrote in message
news:1AB3EA52-EAB2-4C11-82A2-E0A9A97A0B19@.microsoft.com...
> Tibor,
> Thanks for following through. Can you try one more thing that I'm wonderi
ng
> may be having an effect.
> Change you select sql to be a dynamically executed string.
> Like this:
> declare @.sql varchar(255)
> set @.sql = 'select * from x..sysobjects'
> select 'Hello'
> exec(@.sql)
> select 'Hello again'
>
> If that works on your machine, I would say it is something local to mine,
> else I'm wondering if exec() behaves the way I'm expecting it to.
> Thanks again.
>
> "Tibor Karaszi" wrote:
>|||I've been able to run it ok and get the return error value as well... I'm
running SQL 2K5 SP 1 if that makes a difference.
"paul" <paul@.discussions.microsoft.com> wrote in message
news:6897329C-EEFC-4756-A1DA-46E366EAF3EA@.microsoft.com...
> Tried this but didn't work. Is my dos look right?
> !!sqlcmd -U username -P password -S servername -b -V 10 -h-1 -i
> "C:\Admin\bcp table space input file.sql"
> !!echo %ERRORLEVEL%
> with or without -V doesn't work?
>
>
> "Mike C#" wrote:
>|||Tibor / Mike,
Thanks for all your help, I believe I have solved the riddle. As contorted
as my logic might seem, I am actually calling numerous sqlcmd from inside th
e
same transact sql bactch.
To do this I am prefixing it of course with '!!'. So there in lies the
problem, each !!sqlcmd must be spawning its own dos session so if I do the
following:
!!sqlcmd ....
!!echo %errorlevel%
error level will always be 0 in the second dos session.
thanks for helping with that guys
"Tibor Karaszi" wrote:

> Same result. I still managed to catch the error with errorlevel.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:1AB3EA52-EAB2-4C11-82A2-E0A9A97A0B19@.microsoft.com...
>

How to get running stored procs

Is there a script I can run that identifies the stored procedures that
are running NOW?
Regards
On Jun 7, 4:24 am, Frank Rizzo <n...@.none.com> wrote:
> Is there a script I can run that identifies the stored procedures that
> are running NOW?
> Regards
sql profiler show you the current proceses.
|||Hello Frank,
You may need to use the SQL Profiler to track the running stored procedure.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||On Jun 7, 3:24 am, Frank Rizzo <n...@.none.com> wrote:
> Is there a script I can run that identifies the stored procedures that
> are running NOW?
> Regards
Check Erland's website
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
you can use this
|||Execute the profiler and in event selection select "Stored procedures and
select the check box "RPC Completed". After specific time save the contents
into a table and filter it out.
Thanks
Hari
"Frank Rizzo" <none@.none.com> wrote in message
news:OpVP9lIqHHA.3888@.TK2MSFTNGP05.phx.gbl...
> Is there a script I can run that identifies the stored procedures that are
> running NOW?
> Regards
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

How to get running stored procs

Is there a script I can run that identifies the stored procedures that
are running NOW?
RegardsOn Jun 7, 4:24 am, Frank Rizzo <n...@.none.com> wrote:
> Is there a script I can run that identifies the stored procedures that
> are running NOW?
> Regards
sql profiler show you the current proceses.|||Hello Frank,
You may need to use the SQL Profiler to track the running stored procedure.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||On Jun 7, 3:24 am, Frank Rizzo <n...@.none.com> wrote:
> Is there a script I can run that identifies the stored procedures that
> are running NOW?
> Regards
Check Erland's website
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
you can use this|||Execute the profiler and in event selection select "Stored procedures and
select the check box "RPC Completed". After specific time save the contents
into a table and filter it out.
Thanks
Hari
"Frank Rizzo" <none@.none.com> wrote in message
news:OpVP9lIqHHA.3888@.TK2MSFTNGP05.phx.gbl...
> Is there a script I can run that identifies the stored procedures that are
> running NOW?
> Regards|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

How to get running stored procs

Is there a script I can run that identifies the stored procedures that
are running NOW?
RegardsOn Jun 7, 4:24 am, Frank Rizzo <n...@.none.com> wrote:
> Is there a script I can run that identifies the stored procedures that
> are running NOW?
> Regards
sql profiler show you the current proceses.|||Hello Frank,
You may need to use the SQL Profiler to track the running stored procedure.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||On Jun 7, 3:24 am, Frank Rizzo <n...@.none.com> wrote:
> Is there a script I can run that identifies the stored procedures that
> are running NOW?
> Regards
Check Erland's website
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
you can use this|||Execute the profiler and in event selection select "Stored procedures and
select the check box "RPC Completed". After specific time save the contents
into a table and filter it out.
Thanks
Hari
"Frank Rizzo" <none@.none.com> wrote in message
news:OpVP9lIqHHA.3888@.TK2MSFTNGP05.phx.gbl...
> Is there a script I can run that identifies the stored procedures that are
> running NOW?
> Regards|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

How to get invalid stored procedures

Hi, All,
Is there any script or command that can get all invalid stored procedures in
SQL Server 7.0/2000. Thanks
JieWhat do you mean by invalid ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"J Gao" <jie.gao@.tequilasoftware.com> wrote in message
news:%23GbjhauSDHA.2196@.TK2MSFTNGP12.phx.gbl...
Hi, All,
Is there any script or command that can get all invalid stored procedures in
SQL Server 7.0/2000. Thanks
Jie|||Invalid means there is syntax error in the stored procedures. I would like
to have query to get the all stored procedures that have syntax error with
it instead of going to every stored procedure to check sybtax. Thanks
Jasper.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OJO%23aJvSDHA.1552@.TK2MSFTNGP10.phx.gbl...
> What do you mean by invalid ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "J Gao" <jie.gao@.tequilasoftware.com> wrote in message
> news:%23GbjhauSDHA.2196@.TK2MSFTNGP12.phx.gbl...
> Hi, All,
> Is there any script or command that can get all invalid stored procedures
in
> SQL Server 7.0/2000. Thanks
>
> Jie
>
>|||A stored procedure cannot be submitted to SQL Server if it is invalid, with
the exception of delayed name resolution. That could be a problem. Or, if
objects that the procedure uses are dropped, then that would invalidate,
although not delete, the procedure.
There is not a way to test this directly that I know of.
What you could do is:
1. Script out all of the stored procedures using DROP / CREATE.
2. Run that script from OSQL using an output file.
3. Examine the output file for errors.
Any procedures with serious syntax errors will not recreate and will now be
missing from your database. You can examine the errors to see what is wrong
should any of them need fixing.
The downside of this is that you don't want to do it during a productive
work period.
Russell Fields
"J Gao" <jie.gao@.tequilasoftware.com> wrote in message
news:uNzatTvSDHA.3188@.tk2msftngp13.phx.gbl...
> Invalid means there is syntax error in the stored procedures. I would like
> to have query to get the all stored procedures that have syntax error with
> it instead of going to every stored procedure to check sybtax. Thanks
> Jasper.
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OJO%23aJvSDHA.1552@.TK2MSFTNGP10.phx.gbl...
> > What do you mean by invalid ?
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> > "J Gao" <jie.gao@.tequilasoftware.com> wrote in message
> > news:%23GbjhauSDHA.2196@.TK2MSFTNGP12.phx.gbl...
> > Hi, All,
> > Is there any script or command that can get all invalid stored
procedures
> in
> > SQL Server 7.0/2000. Thanks
> >
> >
> > Jie
> >
> >
> >
>|||the only way you can do this is to build a new database
using the scripts created by scripting out your database.
Then you have to worry about dependencies. DB Ghost can do
all of this for you, displaying any syntax errors that may
exist in your database code. It will also show you
dependancy errors that may exist in your code. Both syntax
and dependancy errors can exist in the database code
simply because of inadequate change management. For
example a developer changes a column definition on a table
using an alter statement on that table. However there is a
stored procedure that relies on the original specification
of that column. You now have an error which will only
manifest itself when the stored procedure is next used.
This happens all the time, that's why we use DB Ghost for
our change management as it guarentees the source and the
deployement. Check out www.dbghost.com - it makes a good
read and the app comes with a full help manual.
Mark Baekdal
>--Original Message--
>Hi, All,
>Is there any script or command that can get all invalid
stored procedures in
>SQL Server 7.0/2000. Thanks
>
>Jie
>
>.
>