Comparing DB's columns and create script

Hi,
I am looking for a solution that can generate a script on column difference
between two databases and its tables.
Basically, I have a one core db and one development db. The development db
has been revised several time and now I need to generate a script that just
adds all new table columns with their defaults to the old core db.
Any knowledge about this or some methods to use.
Thanks in advance
ChristianCheck out SQLCompare, from Red-GAte Software... It will compare the two
databases, and generate a script to do exactly what you need...
http://www.red-gate.com
"Christian Perthen" wrote:

> Hi,
> I am looking for a solution that can generate a script on column differenc
e
> between two databases and its tables.
> Basically, I have a one core db and one development db. The development db
> has been revised several time and now I need to generate a script that jus
t
> adds all new table columns with their defaults to the old core db.
> Any knowledge about this or some methods to use.
> Thanks in advance
> Christian
>
>

Comparing 2 dbs for differences in column definitions

Hi All
I have 2 dbs, one for prodcution and the other one for development
environment. They are meant to be the same.
Is there a script/way to compare the dbs for any difference in column
datatypes, length.
This query has what i want to compare:
select
table_name, column_name, data_type, character_maximum_length [Length],
is_nullable [Null], numeric_precision NuPrec, numeric_scale NuScal,
datetime_precision DTPrec
from
information_schema.columns
order by table_name ASC, column_name ASC
Thank you on advance.Maybe you should take a look at Red Gate's SQL Compare.
http://www.red-gate.com/
ML
http://milambda.blogspot.com/|||See replies to the post "Comparing two databases".
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:844E5968-C506-4A25-A410-2128BA6F2741@.microsoft.com...
> Hi All
> I have 2 dbs, one for prodcution and the other one for development
> environment. They are meant to be the same.
> Is there a script/way to compare the dbs for any difference in column
> datatypes, length.
> This query has what i want to compare:
> select
> table_name, column_name, data_type, character_maximum_length [Length],
> is_nullable [Null], numeric_precision NuPrec, numeric_scale NuScal,
> datetime_precision DTPrec
> from
> information_schema.columns
> order by table_name ASC, column_name ASC
> Thank you on advance.
>

Compare Two Queries - Help

Hi All,

I have two database that are duplicates of each other - but are on
different servers.

I need to write a script that will do a select from one table and then
compare it to another select of that table - but on the db on the other
server.

Is it possible to do that in a script? If so, how?

Thanks in advance.Brian Schultz (bdschultz@.gmail.com) writes:

Quote:

Originally Posted by

I have two database that are duplicates of each other - but are on
different servers.
>
I need to write a script that will do a select from one table and then
compare it to another select of that table - but on the db on the other
server.
>
Is it possible to do that in a script? If so, how?

SELECT ...
FROM localtbl l
FULL JOIN SERVER.db.dbo.remotetbl r ON l.keycol = r.keycol
WHERE l.keycol IS NULL
OR r.keycol IS NULL
OR a.col <b.col
OR a.col IS NULL AND b.col IS NOT NULL
OR a.col IS NOT NULL AND b.col IS NULL

SERVER is here a linked server that you have set up with sp_addlinkedserver.

If you need to do this on a large-scale basis, you should probably
consider a third-party product. I believe Red Gate has something called
DataCompare.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||you could try firefly. it's a free tool that i wrote:
http://www.getfirefly.net/
let me know if you have any problems.
thanks,
James

compare table related objects

Hi,
I am migrating a database from one server SQL 6.5 to another SQL 2000. I
want to have script to compare table related objects (index/PK/FK/constraint)
for all the tables in the database to make sure nothing missing.
Anyone can help?
Julia wrote:
> Hi,
> I am migrating a database from one server SQL 6.5 to another SQL
> 2000. I want to have script to compare table related objects
> (index/PK/FK/constraint) for all the tables in the database to make
> sure nothing missing.
> Anyone can help?
I'm not familiar with SQL 6.5, but in SQL 2000 there are the
INFORMATION_SCHEMA views that can be used to obtain a list of tables,
constraints, etc. You could use this to check that everything's there.
John.
|||sql6.x does not have info_ views yet. You will have to look at sysobjects
for the list of objects. If you want to compare definition, you will have to
check out syscomments.
-oj
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:34ACC6D7-4157-4324-9960-2F099809C4A3@.microsoft.com...
> Hi,
> I am migrating a database from one server SQL 6.5 to another SQL 2000. I
> want to have script to compare table related objects
> (index/PK/FK/constraint)
> for all the tables in the database to make sure nothing missing.
> Anyone can help?
>
>
|||I thought that might be the case.
In which case (assuming the migration is straight over and the objects will
either exist and be correct, or be missing), sysobjects with an appropriate
filter on the xtype column sounds ideal - BOL has full documentation on this
column (e.g. a value of U for user table, F for foreign key etc.)
John.
oj wrote:[vbcol=seagreen]
> sql6.x does not have info_ views yet. You will have to look at
> sysobjects for the list of objects. If you want to compare
> definition, you will have to check out syscomments.
>
> "Julia" <Julia@.discussions.microsoft.com> wrote in message
> news:34ACC6D7-4157-4324-9960-2F099809C4A3@.microsoft.com...

compare table related objects

Hi,
I am migrating a database from one server SQL 6.5 to another SQL 2000. I
want to have script to compare table related objects (index/PK/FK/constraint
)
for all the tables in the database to make sure nothing missing.
Anyone can help?Julia wrote:
> Hi,
> I am migrating a database from one server SQL 6.5 to another SQL
> 2000. I want to have script to compare table related objects
> (index/PK/FK/constraint) for all the tables in the database to make
> sure nothing missing.
> Anyone can help?
I'm not familiar with SQL 6.5, but in SQL 2000 there are the
INFORMATION_SCHEMA views that can be used to obtain a list of tables,
constraints, etc. You could use this to check that everything's there.
John.|||sql6.x does not have info_ views yet. You will have to look at sysobjects
for the list of objects. If you want to compare definition, you will have to
check out syscomments.
-oj
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:34ACC6D7-4157-4324-9960-2F099809C4A3@.microsoft.com...
> Hi,
> I am migrating a database from one server SQL 6.5 to another SQL 2000. I
> want to have script to compare table related objects
> (index/PK/FK/constraint)
> for all the tables in the database to make sure nothing missing.
> Anyone can help?
>
>|||I thought that might be the case.
In which case (assuming the migration is straight over and the objects will
either exist and be correct, or be missing), sysobjects with an appropriate
filter on the xtype column sounds ideal - BOL has full documentation on this
column (e.g. a value of U for user table, F for foreign key etc.)
John.
oj wrote:[vbcol=seagreen]
> sql6.x does not have info_ views yet. You will have to look at
> sysobjects for the list of objects. If you want to compare
> definition, you will have to check out syscomments.
>
> "Julia" <Julia@.discussions.microsoft.com> wrote in message
> news:34ACC6D7-4157-4324-9960-2F099809C4A3@.microsoft.com...

compare table related objects

Hi,
I am migrating a database from one server SQL 6.5 to another SQL 2000. I
want to have script to compare table related objects (index/PK/FK/constraint)
for all the tables in the database to make sure nothing missing.
Anyone can help?Julia wrote:
> Hi,
> I am migrating a database from one server SQL 6.5 to another SQL
> 2000. I want to have script to compare table related objects
> (index/PK/FK/constraint) for all the tables in the database to make
> sure nothing missing.
> Anyone can help?
I'm not familiar with SQL 6.5, but in SQL 2000 there are the
INFORMATION_SCHEMA views that can be used to obtain a list of tables,
constraints, etc. You could use this to check that everything's there.
John.|||sql6.x does not have info_ views yet. You will have to look at sysobjects
for the list of objects. If you want to compare definition, you will have to
check out syscomments.
--
-oj
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:34ACC6D7-4157-4324-9960-2F099809C4A3@.microsoft.com...
> Hi,
> I am migrating a database from one server SQL 6.5 to another SQL 2000. I
> want to have script to compare table related objects
> (index/PK/FK/constraint)
> for all the tables in the database to make sure nothing missing.
> Anyone can help?
>
>|||I thought that might be the case.
In which case (assuming the migration is straight over and the objects will
either exist and be correct, or be missing), sysobjects with an appropriate
filter on the xtype column sounds ideal - BOL has full documentation on this
column (e.g. a value of U for user table, F for foreign key etc.)
John.
oj wrote:
> sql6.x does not have info_ views yet. You will have to look at
> sysobjects for the list of objects. If you want to compare
> definition, you will have to check out syscomments.
>
> "Julia" <Julia@.discussions.microsoft.com> wrote in message
> news:34ACC6D7-4157-4324-9960-2F099809C4A3@.microsoft.com...
>> Hi,
>> I am migrating a database from one server SQL 6.5 to another SQL
>> 2000. I want to have script to compare table related objects
>> (index/PK/FK/constraint)
>> for all the tables in the database to make sure nothing missing.
>> Anyone can help?

Compare Script

I need to write a compare script to compare the data that two databases may have in common and to delete the records in the first database. And the records are are not incommon to be moved over to the second database.
Thanks
Lystrahttp://www.sqlscripter.com/|||Thanks, but theres resistions on my PC

I was able to write this:

Select ClientHost,Username,
Logtime,Service, Machine, ServerIP
,ProcessingTime, BytesRecvd,BytesSent,
ServiceStatus, Win32Status, Operation,
Target, Parameters, Department
From IISLOG.dbo.IISLOG as a where NOT EXISTS
(select ClientHost,Username,
Logtime,Service, Machine, ServerIP
,ProcessingTime, BytesRecvd,BytesSent,
ServiceStatus, Win32Status, Operation,
Target, Parameters, Department
from IISLOG as b
Where a.ClientHost =b.ClientHost
and a.Username = b.Username
and a.Logtime = b.Logtime
and a.Service = b.Service
and a.Machine = b.Machine
and a.ServerIP = b.ServerIP
and a.ProcessingTime = b.ProcessingTime
and a.BytesRecvd = b.BytesRecvd
and a.BytesSent = b.BytesSent
and a.serviceStatus = b.ServiceStatus
and a.Win32Status = b.Win32Status
and a.Operation = b.Operation
and a.Target = b.Target
and a.Parameters = b.Parameters
and a.Department = b.Department)|||Don't you have a primary key?|||That's the problem because once the data is archive the Id which is the primary key is not archive. But it does have a ID field in the second database. It kind of like ID autonumber in MS access.

This is the code it uses:

CREATE PROCEDURE sp_archiveLOG AS
declare @.Today varchar(10)
select @.Today = convert(varchar(10),getdate(),101)
--select @.Today = '2002-03-30'
/*
copy updated rows to archive database
*/
insert IISLOG_ARCHIVE2004..iislog
select clienthost,username,logtime,service,machine,server ip,processingtime,bytesrecvd,bytessent,servicestat us,win32status,operation,target,parameters,departm ent from IISLOG..iislog
where department is not null
and ( LogTime >= DateAdd(day, -1, @.Today) AND LogTime < @.Today )
GO

As you see it doesn't archive the id number

The next code deletes the whole record

CREATE PROCEDURE sp_cleanupLOG AS
declare @.Today varchar(10)
select @.Today = convert(varchar(10),getdate(),101)
--select @.Today = '2002-03-30'
/*
delete rows from current iislog table
*/
delete from IISLOG..iislog
where ( LogTime >= DateAdd(day, -1, @.Today) AND LogTime < @.Today )

So for me to compare I would have to compare the whole field.

Lystra

Compare feilds in seperate databases on seperate servers

Hi,
I want to run an update script where a field in a table in a database on a
server is equal to another field in a table in a database on a seperate
server. Here are the details:
Server 1:
Server name - Server1\Logi
Database - Ascent
Table - _customers
Field - email
Server 2:
Server name - Server2\Web
Database - ProductCart
Table - customers
Field - email
I want to update a field called 'flag' to equal 1 where the email addresses
are equal.
Can anyone help?To do this right we'd have to see more DDL - particulary interested in keys.
ML
http://milambda.blogspot.com/|||Hi
assuming you are on Server1\Logi, you can do something like
sp_addlinkedserver Web, @.srvproduct='', @.provider='SQLNCLI',
@.datasrc='Server2\WEB'
update _customers set flag = 1
from _customers a inner join Web.ProductCart.dbo.customers b on a.email =
b.email
exec sp_dropserver Web
Note that you should take care about proper credentials when connecting to
the linked server - read BOL about sp_addlinkedserver sproc. It is possible
also that it might be better to join tables on other column[s], but you did
not give any info on this.
HTH
Peter|||Hi Peter,
Thanks for the reply. I ran your script but got the following error:
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for equal to operation.
Any ideas what this means?
Darren
"Rogas69" <rogas69@.no_spamers.o2.ie> wrote in message
news:OAH9afjAGHA.1460@.TK2MSFTNGP14.phx.gbl...
> Hi
> assuming you are on Server1\Logi, you can do something like
> sp_addlinkedserver Web, @.srvproduct='', @.provider='SQLNCLI',
> @.datasrc='Server2\WEB'
> update _customers set flag = 1
> from _customers a inner join Web.ProductCart.dbo.customers b on a.email =
> b.email
> exec sp_dropserver Web
> Note that you should take care about proper credentials when connecting to
> the linked server - read BOL about sp_addlinkedserver sproc. It is
> possible also that it might be better to join tables on other column[s],
> but you did not give any info on this.
> HTH
> Peter
>|||You have to make sure that they are using the same collation to join
them, sample below:
Select * FROM
sometable localtable
Inner join
SomeotherServer.Database.Owner.SomeTable linkedtable
WHERE linkedserv.Somecolumn COLLATE SQL_Latin1_General_CP1_CI_AI =3D
localtable.somecolumn COLLATE SQL_Latin1_General_CP1_CI_AI
Normally you don=B4t need to specify that on both sides if you just
specify the collation on the side that is different to this on your
local server and vice cersa.
HTH, jens Suessmeyer.|||This means that the two databases use different collations. Look up
collations in Books Online, there you'll also find the COLLATE keyword, whic
h
you can use to solve the problem.
Something like that:
update _customers set flag = 1
from _customers a
inner join Web.ProductCart.dbo.customers b
on a.email = b.email collate <collation
name>
The collation name is displayed in database properties in Enterprise manager
.
ML
http://milambda.blogspot.com/|||Thanks guys, i'll go check it out.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:70933017-66F0-4438-B8EE-E5824B77E3C9@.microsoft.com...
> This means that the two databases use different collations. Look up
> collations in Books Online, there you'll also find the COLLATE keyword,
> which
> you can use to solve the problem.
> Something like that:
> update _customers set flag = 1
> from _customers a
> inner join Web.ProductCart.dbo.customers b
> on a.email = b.email collate <collation
> name>
> The collation name is displayed in database properties in Enterprise
> manager.
>
> ML
> --
> http://milambda.blogspot.com/

Compare data in tow users

Does anybody have a script that would be able to compare two users (oracle)
or dB (MSSQL) tables by table, assuming that schema is identical?
Thx
YuriWYou want a tool like SQL Data compare:
http://www.red-gate.com/sql/summary.htm
-- Keith
"Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message =news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> Does anybody have a script that would be able to compare two users =(oracle)
> or dB (MSSQL) tables by table, assuming that schema is identical?
> > Thx
> > YuriW
> >|||I'd rather prefer a sql script.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uG6cjlzkDHA.2964@.tk2msftngp13.phx.gbl...
You want a tool like SQL Data compare:
http://www.red-gate.com/sql/summary.htm
--
Keith
"Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> Does anybody have a script that would be able to compare two users
(oracle)
> or dB (MSSQL) tables by table, assuming that schema is identical?
> Thx
> YuriW
>|||Check the following article:
http://www.sql-server-performance.com/vg_database_comparison_sp.asp
-Sue
On Wed, 15 Oct 2003 17:13:13 GMT, "Yuri Weinstein"
<yuriw@.hotmail.com> wrote:
>I'd rather prefer a sql script.
>"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
>news:uG6cjlzkDHA.2964@.tk2msftngp13.phx.gbl...
>You want a tool like SQL Data compare:
>http://www.red-gate.com/sql/summary.htm|||in oracle, generate a script from the data dictionary, something like this:
select 'select * from user1.'||table_name|| ' minus select * from user2.' ||
table_name || ';'
from dba_tables where owner = 'USER1';
this show any rows in USER1 that don't exist in USER2 (or have slight
differences)
you could also generate a join or a more complex comparison statement,
depending on what you're looking for
either spool out the output in SQL*Plus, or but this in a PL/SQL block and
use EXECUTE IMMEDIATE to run the generated statements
note that this will not work for tables that have LONG columns
---
Mark C. Stock
www.enquery.com
(888) 512-2048
"Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> Does anybody have a script that would be able to compare two users
(oracle)
> or dB (MSSQL) tables by table, assuming that schema is identical?
> Thx
> YuriW
>|||SQL queries are not a terribly good tool for doing this type of comprison.
It would be easier to simply dump out the tables (BCP for MS SQL Server)
into two directories, one for each database. Then, you can compare the
tables -- i.e. their exported files -- using one of many file comparison
tools.
Also, there are commercial tools such as SQL Compare from Red Gate.
I do this myself with Perl scripts. If you are interested, email me and I'll
drop you a copy.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"mcstock" <mcstock@.enquery.com> wrote in message
news:KK6dnf9cybED0gWiRVn-jA@.comcast.com...
> in oracle, generate a script from the data dictionary, something like
this:
> select 'select * from user1.'||table_name|| ' minus select * from user2.'
||
> table_name || ';'
> from dba_tables where owner = 'USER1';
> this show any rows in USER1 that don't exist in USER2 (or have slight
> differences)
> you could also generate a join or a more complex comparison statement,
> depending on what you're looking for
> either spool out the output in SQL*Plus, or but this in a PL/SQL block and
> use EXECUTE IMMEDIATE to run the generated statements
> note that this will not work for tables that have LONG columns
> ---
> Mark C. Stock
> www.enquery.com
> (888) 512-2048
>
> "Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
> news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> > Does anybody have a script that would be able to compare two users
> (oracle)
> > or dB (MSSQL) tables by table, assuming that schema is identical?
> >
> > Thx
> >
> > YuriW
> >
> >
>|||thx, Mark.
"mcstock" <mcstock@.enquery.com> wrote in message
news:KK6dnf9cybED0gWiRVn-jA@.comcast.com...
> in oracle, generate a script from the data dictionary, something like
this:
> select 'select * from user1.'||table_name|| ' minus select * from user2.'
||
> table_name || ';'
> from dba_tables where owner = 'USER1';
> this show any rows in USER1 that don't exist in USER2 (or have slight
> differences)
> you could also generate a join or a more complex comparison statement,
> depending on what you're looking for
> either spool out the output in SQL*Plus, or but this in a PL/SQL block and
> use EXECUTE IMMEDIATE to run the generated statements
> note that this will not work for tables that have LONG columns
> ---
> Mark C. Stock
> www.enquery.com
> (888) 512-2048
>
> "Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
> news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> > Does anybody have a script that would be able to compare two users
> (oracle)
> > or dB (MSSQL) tables by table, assuming that schema is identical?
> >
> > Thx
> >
> > YuriW
> >
> >
>
>|||Hi Mark,
I am alomst there, but just not yet. Can you help me with this sql.
Here are exact steps:
1. set pages 5555 (trying to get all garbidge from temp.sql and it does not
do it?)
2. spool temp.sql
3. select 'select count(*) from magnetic.' || table_name ||';' from
dba_tables
where owner = 'MAGNETIC' ORDER BY table_name;
This statement works.
4. spool off
5. @.temp.sql (with an exception some errors like I said in #1).
Now if I run -
select 'select count(*) from magnetic.' || table_name ||';' from dba_tables
where owner = 'MAGNETIC' ORDER BY table_name||';'||select 'select count(*)
from
magnetic.' || table_name ||';' from dba_tables where owner = 'MAGNETIC'
ORDER B
Y table_name;
I get - ORA-00936: missing expression
Where is the error in sql?
Thanks in advance.
YuriW
"mcstock" <mcstock@.enquery.com> wrote in message
news:KK6dnf9cybED0gWiRVn-jA@.comcast.com...
> in oracle, generate a script from the data dictionary, something like
this:
> select 'select * from user1.'||table_name|| ' minus select * from user2.'
||
> table_name || ';'
> from dba_tables where owner = 'USER1';
> this show any rows in USER1 that don't exist in USER2 (or have slight
> differences)
> you could also generate a join or a more complex comparison statement,
> depending on what you're looking for
> either spool out the output in SQL*Plus, or but this in a PL/SQL block and
> use EXECUTE IMMEDIATE to run the generated statements
> note that this will not work for tables that have LONG columns
> ---
> Mark C. Stock
> www.enquery.com
> (888) 512-2048
>
> "Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
> news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> > Does anybody have a script that would be able to compare two users
> (oracle)
> > or dB (MSSQL) tables by table, assuming that schema is identical?
> >
> > Thx
> >
> > YuriW
> >
> >
>
>|||"Yuri Weinstein (HotMail)" wrote...
> Now if I run -
> select 'select count(*) from magnetic.' || table_name ||';' from
dba_tables
> where owner = 'MAGNETIC' ORDER BY table_name||';'||select 'select
count(*)
---^
> from
> magnetic.' || table_name ||';' from dba_tables where owner ='MAGNETIC'
> ORDER B
> Y table_name;
> I get - ORA-00936: missing expression
> Where is the error in sql?
>
Hi Yuri,
it seems to me that you try to construct a string of 2 selects
in a select statement. But if I'm right, you have some problems
with quotes. Your statement is quite ok until you try to put
a concatenation after the ORDER BY.
I believe you want to put the from and order by clauses into
to string.
Try to work from here.
hth,
Guido

Compare 2 database schemas?

We're using Sql Server 2000. The one database contained tables and stored procedures which were possibly updated with some script information. Is there an application(commercial or free) or script I can use to compare the base database against this updated database to confirm there schema information is the same.Thanks

You can use the tool provide by Microsoft "Visual Studio Team Edition for Database Professionals"

This will give you Comparison Tools (Schema & Data Compare) allow comparisons & synchronization of schema and data with design/test/production databases

Have a look into the webcast at following url

http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032300980

This will give you a good idea

Communication between tasks in an SSIS Package

Hi,
I have a Flat File Source and I want to retrieve few properties of it in an Script Component. How do I?

Also, How could I make the file path of Flat File Source or Connection manager dynamic or configurable through some file ?

any input is appreciated.

Fahad

Fahad349 wrote:

Hi,
Also, How could I make the file path of Flat File Source or Connection manager dynamic or configurable through some file ?

Fahad

Look at package configurations. The forum search will be your friend.

Common Password

Hi, I would like to setup a common password for all the MS SQL Server Users. Is there any generalized script for the same ? Thanks In Advance.

No, there is no such script that ships with SQL Server because we do not recommend setting a common password for all SQL logins.

Thanks
Laurentiu

|||Thanks, Let me put my question right -

How to change a user's password through a System SP ? If I only know the administrator's username and password.|||

If you are an administrator and you want to reset a user's password, then you can just execute:

ALTER LOGIN user WITH PASSWORD = 'new_password'

See the ALTER LOGIN topic in BOL for more info on this statement.

Is this what you were looking for?

Laurentiu

|||Thanks Again. Yes you are right but i tried using the Statement and it does not work. I did not find anything related in BOL.

Note: I am using MS SQL Server 2000. Thanks|||In SQL Server 2000, you can use sp_password. CREATE LOGIN is new DDL available in SQL Server 2005 only. Here's the equivalent syntax using sp_password:

sp_password NULL, 'new_password', 'user'

You can search for "sp_password" in BOL for SQL Server 2000 for additional info on this.

Thanks
Laurentiu|||Thanks Lots Laurentiu.

Command Line SQL Server Shutdown??

I need to write a script to run at a specific time to shutdown a
SQL Server. I also need to start the SQL Server back up.
Does anyone know the command line that could be used in a BAT script
to shutdown and then restart a SQL Server v.7
Please reply via email and post
Thanks in Advance,
JohnJohn,
You can use NET STOP and NET START, as in
net stop MSSQLServer
net start MSSQLServer
Please note that if sqlserveragent is dependent, then stop that service
first before stopping SQLServer service.These commands are documented in
BooksOnLine.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"John" <jkimb@.kimberlyconsulting.com> wrote in message
news:pan.2004.05.07.21.19.25.367752@.kimberlyconsulting.com...
> I need to write a script to run at a specific time to shutdown a
> SQL Server. I also need to start the SQL Server back up.
> Does anyone know the command line that could be used in a BAT script
> to shutdown and then restart a SQL Server v.7
> Please reply via email and post
> Thanks in Advance,
> John
>|||"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:uEvi3qHNEHA.2064@.TK2MSFTNGP12.phx.gbl...
> John,
> You can use NET STOP and NET START, as in
> net stop MSSQLServer
> net start MSSQLServer
> Please note that if sqlserveragent is dependent, then stop that service
> first before stopping SQLServer service.These commands are documented in
> BooksOnLine.
In that case net stop mssqlserver /y also stops dependent services

Command Line SQL Server Shutdown??

I need to write a script to run at a specific time to shutdown a
SQL Server. I also need to start the SQL Server back up.
Does anyone know the command line that could be used in a BAT script
to shutdown and then restart a SQL Server v.7
Please reply via email and post
Thanks in Advance,
John
John,
You can use NET STOP and NET START, as in
net stop MSSQLServer
net start MSSQLServer
Please note that if sqlserveragent is dependent, then stop that service
first before stopping SQLServer service.These commands are documented in
BooksOnLine.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"John" <jkimb@.kimberlyconsulting.com> wrote in message
news:pan.2004.05.07.21.19.25.367752@.kimberlyconsul ting.com...
> I need to write a script to run at a specific time to shutdown a
> SQL Server. I also need to start the SQL Server back up.
> Does anyone know the command line that could be used in a BAT script
> to shutdown and then restart a SQL Server v.7
> Please reply via email and post
> Thanks in Advance,
> John
>
|||"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:uEvi3qHNEHA.2064@.TK2MSFTNGP12.phx.gbl...
> John,
> You can use NET STOP and NET START, as in
> net stop MSSQLServer
> net start MSSQLServer
> Please note that if sqlserveragent is dependent, then stop that service
> first before stopping SQLServer service.These commands are documented in
> BooksOnLine.
In that case net stop mssqlserver /y also stops dependent services

Command line parameter passing

Command line parameter passing
Does anyone know if it is possible to pass a parameter to a script run
from the isql (Query Analyzer).
I have a large .SQL file that does a setup of a database and I would
like to pass it a parameter that is
A name of another database to create some views to.
If its not possible from the command line, Is it possible to set an
environment variable and check that
variable inside the .sql script.
Any help would be appreciated.You can use the OSQL command-line utility and pass a parameter as part of a
the query / command string (-q switch).
Lookup the OSQL syntax in Books Online.
David Portas
SQL Server MVP
--|||Thanks for the response.
I have been trying something like this.
isql -H%GSHost% -S%GSServer% -d%GSDB% -U%GSUser% -P%GSPassWord% -n -q
"declare @.Odb varchar(40);set @.Odb=' test ';print @.Odb" -i GsViews.sql
in the GSViews.sql I do
print @.Odb
So when I do this the first print (from the -q) works but the one in the
GSViews.sql (run by the -i) errors out stating that @.Odb does not exist.
No GSViews.sql is quite long and it needs to know what other database
name. Any idea?
David Portas wrote:

> You can use the OSQL command-line utility and pass a parameter as part of
a
> the query / command string (-q switch).
> Lookup the OSQL syntax in Books Online.
>

Command line or tsql script for datbase compaare

Need a command line tool or TSQL script for database
structure compare.
Thanks
Y. Abesi wrote:
> Need a command line tool or TSQL script for database
> structure compare.
> Thanks
You can check out our Speed Change Manager: www.imceda.com
David Gugick
Imceda Software
www.imceda.com
|||There is an article and free script on how to compare
databases at sql-server-performance.com. It's a pretty cool
script to go through even if you don't end up using it:
http://www.sql-server-performance.co...parison_sp.asp
-Sue
On Thu, 3 Feb 2005 09:46:24 -0800, "Y. Abesi"
<younes@.healthcaresourc.com> wrote:

>Need a command line tool or TSQL script for database
>structure compare.
>Thanks
|||Personally I like the SQL Compare Tools found at
http://www.red-gate.com/sql/summary.htm. Granted they're not free, but the
work very well.
"Y. Abesi" wrote:

> Need a command line tool or TSQL script for database
> structure compare.
> Thanks
>
|||check out DB Ghost (http://www.dbghost.com) which isn't just comparison but a
complete approach to database change management.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Y. Abesi" wrote:

> Need a command line tool or TSQL script for database
> structure compare.
> Thanks
>

Command Line connect to SQL Server Database

I am trying to connect to the SQL Server database from the command line or from a cmd script on Windows - Is there a way to do this?

I am new to SQL Server and I am trying to create a script that can check to see if the database is up and then send me a message if it is down.Yes, you can connect to the SQL Server from the command line by means of the BCP utility for example.

Here is a list of the various CMD utilities for SQL 2000. Haven't tried them for SQL 2005 but i suppose they either work or there is something simmilar around.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_intro_3nsj.asp

As to inform you on the status of the SQL Server, you could create a Job in the SQL Server Agent to check for a simple query such as "SELECT @.@.SERVERNAME" and send an e-mail when it fails.
You can also set the SQL Server Agent service to auto-restart and create another Job to start every time the SQL Server Agent starts and tell the job to send an e-mail, so you can check for problems with the SQL Server Agent.

Hope this helps.
Good luck,
Alan.|||

osql.exe is command line client which allows you to establish a connection to SQL Server.

Please try
osql -E
for trusted connection, or
osql -U<login id> -P<password> -S<instance name>

you can skip -S parameter if you have installed a default instance.

In addition, use
osql -U<login id> -P<password> -S<instance name> -i<tsql script file name>
to run your scripts.

HTH.

MCW

|||Thanks for pointing me to the CMD utilities.|||Thanks this was very helpfull I was able to login from the cmd prompt.