How to Connect to SQL Server 200 objects

I using VB.NET 2005

I want to connect to my LAN SQL Server 2000 objects

using SMO How I can do that

please any answer

I using SQL Server Management Studio Express CTP

and How I can connect to My LAN SQL Server 2000 Instances

should I write "Computer_name\Instance_name" or What ?

Please My MAIN OBJECTIVE IS TO CONNECT TO SQL Server 2000 Instace ?

You should see all instances through the

EnumAvailableSqlServers

method including the instances from SQL Server 2000.

Computer_name\Instance_name is only needed if you connect to a named instance (either in 2k or 2k5). If you are connecting to a default instance you don′t need the named instance syntax.

HTH, Jens Suessmeyer.

How to configure Smo.Scripter to get desired sprocs

Hi, I've developed a sproc generator using SMO types and all works fine except when I want to generate sprocs prefaced by a drop statement. To do this I create an instance of the Scripter object and set it's options.ScriptDrops property to true ( instance.options.ScriptDrops = true).

When options.ScriptDrops = true the output omits the actual stored procedure from the scriopt. When I comment out scriptDrops = true it works correctly. How do i get it working correctly WITH drop statements?

Example:

private void CreateScripter() {
if( this._SprocSscripter == null ) {
this._SprocSscripter = new Scripter();
this._SprocSscripter.Server = this._tables.Parent.Parent;
this._SprocSscripter.Options.IncludeHeaders = true;
this._SprocSscripter.Options.IncludeIfNotExists = true;
this._SprocSscripter.Options.DdlBodyOnly = false;
this._SprocSscripter.Options.DdlHeaderOnly = false;
this._SprocSscripter.Options.ExtendedProperties = true;
this._SprocSscripter.Options.ScriptDrops = true;
}
}

OUTPUT:

/****** Object: StoredProcedure [dbo].[usp_selectCategories] Script Date: 01/18/2007 16:39:29 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_selectCategories]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_selectCategories]

OUPUT when i comment out last line ( this._SprocSscripter.Options.ScriptDrops = true;)

/****** Object: StoredProcedure [dbo].[usp_selectCategories] Script Date: 01/18/2007 16:37:04 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_selectCategories]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'CREATE PROCEDURE [dbo].[usp_selectCategories]
@.CategoryID [int]
AS
SELECT [CategoryID],
[CategoryName],
[Description],
[Picture]
FROM [dbo].[Categories]
WHERE [CategoryID] = @.CategoryID
'
END

If I Comment out all lines in the CreateScripter method exect the 1st, 2nd and last ( this._SprocSscripter.Options.ScriptDrops = true;)
The output looks like this:

DROP PROCEDURE [dbo].[usp_selectCategories]

That's it-- no sproc body.. what gives?

Bill Graziano did a great job of documenting how to do this at his SQLTeam.com web site - here's the link to the article:

http://www.sqlteam.com/item.asp?ItemID=23185

|||Excellent. Thank you. Too bad it's a bug, but learning that it is has restored some of my sanity :-)|||

|||

How to configure Smo.Scripter to get desired sprocs

Hi, I've developed a sproc generator using SMO types and all works fine except when I want to generate sprocs prefaced by a drop statement. To do this I create an instance of the Scripter object and set it's options.ScriptDrops property to true ( instance.options.ScriptDrops = true).

When options.ScriptDrops = true the output omits the actual stored procedure from the scriopt. When I comment out scriptDrops = true it works correctly. How do i get it working correctly WITH drop statements?

Example:

private void CreateScripter() {
if( this._SprocSscripter == null ) {
this._SprocSscripter = new Scripter();
this._SprocSscripter.Server = this._tables.Parent.Parent;
this._SprocSscripter.Options.IncludeHeaders = true;
this._SprocSscripter.Options.IncludeIfNotExists = true;
this._SprocSscripter.Options.DdlBodyOnly = false;
this._SprocSscripter.Options.DdlHeaderOnly = false;
this._SprocSscripter.Options.ExtendedProperties = true;
this._SprocSscripter.Options.ScriptDrops = true;
}
}

OUTPUT:

/****** Object: StoredProcedure [dbo].[usp_selectCategories] Script Date: 01/18/2007 16:39:29 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_selectCategories]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_selectCategories]

OUPUT when i comment out last line ( this._SprocSscripter.Options.ScriptDrops = true;)

/****** Object: StoredProcedure [dbo].[usp_selectCategories] Script Date: 01/18/2007 16:37:04 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_selectCategories]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'CREATE PROCEDURE [dbo].[usp_selectCategories]
@.CategoryID [int]
AS
SELECT [CategoryID],
[CategoryName],
[Description],
[Picture]
FROM [dbo].[Categories]
WHERE [CategoryID] = @.CategoryID
'
END

If I Comment out all lines in the CreateScripter method exect the 1st, 2nd and last ( this._SprocSscripter.Options.ScriptDrops = true;)
The output looks like this:

DROP PROCEDURE [dbo].[usp_selectCategories]

That's it-- no sproc body.. what gives?

Bill Graziano did a great job of documenting how to do this at his SQLTeam.com web site - here's the link to the article:

http://www.sqlteam.com/item.asp?ItemID=23185

|||Excellent. Thank you. Too bad it's a bug, but learning that it is has restored some of my sanity :-)

how to CHECK_POLICY = OFF

sorry for my bad english - i am working with some piece of code in SMO i do not understand well
(and rather had no time to understand it well) - this cpp-winapi-smo code was a part of instalator used to install
MSDE on client computer - It restore database from .backup file - and then configure dtabase, particulary
create few logins on restored database. -- I try to only change MSDE instalation on sqlex2k5 instalation
and encounter a problem mentioned in internet also - mainly - this code (i put here a fragmnt)

//create logins
Log(" Creating logins...");
debuglog(117,"try");
try
{
_LoginPtr pL;
if (SUCCEEDED(pL.CreateInstance("SQLDMO.Login")))
{
pL->Name = "flogin";
debuglog(118,"pL->Type = SQLDMOLogin_Standard;");
pL->Type = SQLDMOLogin_Standard;
pL->Database = m_sDatabase.AllocSysString();

try {
Log(" TTX ");

pSrv->GetLogins()->Add(pL); }

// <--

catch (_com_error err) {
Log("|| Exception during create flogin login");
Log("|| ErrorMessage: " + CString(err.ErrorMessage()));
debuglog(119,"CString sDesc = CW2A(err.Description());");
CString sDesc = CW2A(err.Description());
Log("|| ErrorDescription: " + sDesc);
(*dwExitCode) = err.WCode();
(*sErrorDescription) = sDesc;
}
pSrv->GetLogins()->Item("flogin")->SetPassword("", "jpnzpkzcnobwso");
pSrv->GetServerRoles()->Item("dbcreator")->AddMember("flogin");
Log(" Created 'flogin' login");
debuglog(120,"");
}
else
{
Log(" Creating 'flogin' login FAILED");
}

cannot create login becouse of CHECK_POLICY change in sql2k5 (as far as I know)
As far as I read I should execute from this kode sql line similiar to this "CREATE LOGIN bob WITH PASSWORD = 'password', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF" or do such thing in some SMO way
if possible but had no special idea how to do this one (sql) or the second (SMO)

MAybe someone could help me a bit with this.
Kenobi

There is a boolean property on the Login object called PasswordPolicyEnforced() which you'll want to set to false before creating the login.

That should solve your problem.

how to CHECK_POLICY = OFF

sorry for my bad english - i am working with some piece of code in SMO i do not understand well
(and rather had no time to understand it well) - this cpp-winapi-smo code was a part of instalator used to install
MSDE on client computer - It restore database from .backup file - and then configure dtabase, particulary
create few logins on restored database. -- I try to only change MSDE instalation on sqlex2k5 instalation
and encounter a problem mentioned in internet also - mainly - this code (i put here a fragmnt)

//create logins
Log(" Creating logins...");
debuglog(117,"try");
try
{
_LoginPtr pL;
if (SUCCEEDED(pL.CreateInstance("SQLDMO.Login")))
{
pL->Name = "flogin";
debuglog(118,"pL->Type = SQLDMOLogin_Standard;");
pL->Type = SQLDMOLogin_Standard;
pL->Database = m_sDatabase.AllocSysString();

try {
Log(" TTX ");

pSrv->GetLogins()->Add(pL); }

// <--

catch (_com_error err) {
Log("|| Exception during create flogin login");
Log("|| ErrorMessage: " + CString(err.ErrorMessage()));
debuglog(119,"CString sDesc = CW2A(err.Description());");
CString sDesc = CW2A(err.Description());
Log("|| ErrorDescription: " + sDesc);
(*dwExitCode) = err.WCode();
(*sErrorDescription) = sDesc;
}
pSrv->GetLogins()->Item("flogin")->SetPassword("", "jpnzpkzcnobwso");
pSrv->GetServerRoles()->Item("dbcreator")->AddMember("flogin");
Log(" Created 'flogin' login");
debuglog(120,"");
}
else
{
Log(" Creating 'flogin' login FAILED");
}

cannot create login becouse of CHECK_POLICY change in sql2k5 (as far as I know)
As far as I read I should execute from this kode sql line similiar to this "CREATE LOGIN bob WITH PASSWORD = 'password', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF" or do such thing in some SMO way
if possible but had no special idea how to do this one (sql) or the second (SMO)

MAybe someone could help me a bit with this.
Kenobi

There is a boolean property on the Login object called PasswordPolicyEnforced() which you'll want to set to false before creating the login.

That should solve your problem.