Outlook

Hi,
try to use xp_sendmail. but you don't need the database in
single user to run dbcc checkdb, you only need single user
mode if you use the repair option.
CMLC

>--Original Message--
>Folks,
>Can you point me to where I can look for more info to
complete this task.
>There is a proprietary app running with SQL 2000 as
backend. Everynight the
>app put DB in single user mode and runs DBCC itself to
check data. That
>runs well except few times it failed to get exclusive
login in order to
>clear single user mode. It causes the app fails to
open. I think I may
>want to run a script at my computer querying the DB and
notify someone by
>using Outlook on my computer. Is there any info that can
tell me how to
>open Outlook from in inside sql script?
>Thanks,
>
>.
>
hi,
to use xp_sendmail we must have SQL mail configured on the SQL box. This is
not my case because I can't touch the box. What I wanted is to use Outlook
on my pc to send email out. Is it possible?

> you don't need the database in single user to run dbcc checkdb, you only
> need single user mode if you use the >repair option
the app is proprietary so ... no touching. It seems that it would repair
data if possible.
"CMLC" <anonymous@.discussions.microsoft.com> wrote in message
news:196d01c4a188$bc87d820$a401280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> try to use xp_sendmail. but you don't need the database in
> single user to run dbcc checkdb, you only need single user
> mode if you use the repair option.
> CMLC
>
> complete this task.
> backend. Everynight the
> check data. That
> login in order to
> open. I think I may
> notify someone by
> tell me how to

Ouestion on port 1433/1434

Hi
Is it wise to open port 1433/1434 so that a remote user can access sql server thats on my dmz
ThanksBetter to use non-standard ports, if possible.
Sincerely,
Invotion Engineering Team
Advanced Microsoft Hosting Solutions
http://www.Invotion.com
>--Original Message--
>Hi,
>Is it wise to open port 1433/1434 so that a remote user
can access sql server thats on my dmz?
>Thanks
>.
>|||Hi
Where can I find info on changing the ports
Thanks

OT: Toronto SQL Server User Group

Hi all,
A bit OT, but does Toronto have an active SQL Server User Group?
www.tsql.ca seems to be down...
Thanks!
--
spamhoneypot@.rogers.com (Do not e-mail)Yes, it is active. I'll try and ping the guys and find out what happened.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Spam Catcher" <spamhoneypot@.rogers.com> wrote in message
news:Xns9A49966649663usenethoneypotrogers@.127.0.0.1...
Hi all,
A bit OT, but does Toronto have an active SQL Server User Group?
www.tsql.ca seems to be down...
Thanks!
--
spamhoneypot@.rogers.com (Do not e-mail)|||I found out that it is a hardware issue and it should be up tomorrow.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Spam Catcher" <spamhoneypot@.rogers.com> wrote in message
news:Xns9A49966649663usenethoneypotrogers@.127.0.0.1...
Hi all,
A bit OT, but does Toronto have an active SQL Server User Group?
www.tsql.ca seems to be down...
Thanks!
--
spamhoneypot@.rogers.com (Do not e-mail)

OT: Toronto SQL Server User Group

Hi all,
A bit OT, but does Toronto have an active SQL Server User Group?
www.tsql.ca seems to be down...
Thanks!
spamhoneypot@.rogers.com (Do not e-mail)
Yes, it is active. I'll try and ping the guys and find out what happened.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Spam Catcher" <spamhoneypot@.rogers.com> wrote in message
news:Xns9A49966649663usenethoneypotrogers@.127.0.0. 1...
Hi all,
A bit OT, but does Toronto have an active SQL Server User Group?
www.tsql.ca seems to be down...
Thanks!
spamhoneypot@.rogers.com (Do not e-mail)
|||I found out that it is a hardware issue and it should be up tomorrow.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Spam Catcher" <spamhoneypot@.rogers.com> wrote in message
news:Xns9A49966649663usenethoneypotrogers@.127.0.0. 1...
Hi all,
A bit OT, but does Toronto have an active SQL Server User Group?
www.tsql.ca seems to be down...
Thanks!
spamhoneypot@.rogers.com (Do not e-mail)

osql batch

Hi
I want to run a batch file containg osql commands but do not want the user
to be able to see the sa password. Is there any way of doing this?
Thanks> I want to run a batch file containg osql commands but do not want the user
> to be able to see the sa password. Is there any way of doing this?
Use trusted connection (-E parameter) instead of sql login/password.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks
"Dejan Sarka" wrote:
> > I want to run a batch file containg osql commands but do not want the user
> > to be able to see the sa password. Is there any way of doing this?
> Use trusted connection (-E parameter) instead of sql login/password.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

Osql

Hi all.

My first post here.

I want to distriburte a msde database. When doing so, I also want to make a new login, make a user, make the user the dbowner of the database i installed.

I found the Stored procedures I need to use, and I have tested that it works using OSQL.

What I want to do now is to make this automatic. After installing the database the OSQL commands should be executed and no user interference should be necessary. How can that be done ?

peetthrough a *.bat file?|||through a *.bat file?

Ok, how ?

From what I see I have to do the following :

'**************** sample ********************

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

D:\>osql -S PEETSPC\PEETSQL -U sa
Password:
1> sp_attach_single_file_db "TEST","D:\DB\TEST.MDF"
2> go
New log file 'D:\DB\TEST_log.LDF' was created.
1> exit

D:\>osql -S PEETSPC\PEETSQL -U sa -d TEST
Password:
1> sp_addlogin test, mypass, TEST
2> sp_adduser test, test, db_owner
3> go
1> sp_addlogin test, mypass, TEST
2> go
New login created.
1> sp_adduser test, test, db_owner
2> go
Granted database access to 'test'.
'test' added to role 'db_owner'.
1> exit

D:\>

How can I do this using a bat file ?
I know what a bat file is, but I do not see how I can get a bat file to do the above. Please help me out if you can :-)

peet|||*bump*

anyone ?|||You need two separate files. The first one I'll calll test.sql will contain:sp_attach_single_file_db "TEST","D:\DB\TEST.MDF"
go

USE TEST
GO

sp_addlogin test, mypass, TEST
sp_adduser test, test, db_owner
go

sp_addlogin test, mypass, TEST
go

sp_adduser test, test, db_owner
goThe second file I'll call test.bat:osql -S PEETSPC\PEETSQL -U sa - P myPass=123 -i test.sql -o test.rptWhen you run the batch file, it will create the database, add the logins and users, and put the output into the test.rpt file for your reading pleasure.

-PatP

OSQL

Creating a utility that will allow a user to run a query from a BAT file.
BUT the SQL client is not installed on all PCs. How can I modify this to
run, or what must I do in order for this to work from PCs with no SQL client
'
osql -E -S EXCEDE\EXCEDE -d excede_Reporting -q"EXIT(exec isp_SetCrPw %1)"Hi,
you would probably have to write your own application for this using
e.g. .NET. That should be quite easy, as the only thing would be to
have a argument parser in place, the rest is only some simply
SqlConnection and SqlCommand.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||That would be beyond my current abilities. I can write some very simple
scripts, and I have a mild understanding of some VB and .NET, But to write
my own code. Well I have no idea where to start. If you would like to help
and get me started I would greatly appreciate and enjoy the experience.
Thanks.
==============================================="Jens" <Jens@.sqlserver2005.de> wrote in message
news:1171392808.470036.289720@.j27g2000cwj.googlegroups.com...
> Hi,
> you would probably have to write your own application for this using
> e.g. .NET. That should be quite easy, as the only thing would be to
> have a argument parser in place, the rest is only some simply
> SqlConnection and SqlCommand.
>
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||Lets take that offline and write me an Email.|||WANNABE;7269311 Wrote:
> That would be beyond my current abilities. I can write some very
> simple
> scripts, and I have a mild understanding of some VB and .NET, But to
> write
> my own code. Well I have no idea where to start. If you would like to
> help
> and get me started I would greatly appreciate and enjoy the
> experience.
> Thanks.
> ===============================================> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1171392808.470036.289720@.j27g2000cwj.googlegroups.com...
> > Hi,
> >
> > you would probably have to write your own application for this using
> > e.g. .NET. That should be quite easy, as the only thing would be to
> > have a argument parser in place, the rest is only some simply
> > SqlConnection and SqlCommand.
> >
> >
> > HTH, Jens K. Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --
> >
I have written an OSQL like replacement in C#/.Net 2.0. I am
having a wee bit of an issue though. If the SQL file to be run
has dynamic SQL in it (building SQL strings and then executing
them) then the command will fail - always. I think it has
something to do with the nesting of the quotes. Got any ideas
on what would be causing something like this?
Thanks
escher4096

OSQL

Creating a utility that will allow a user to run a query from a BAT file.
BUT the SQL client is not installed on all PCs. How can I modify this to
run, or what must I do in order for this to work from PCs with no SQL client
?
osql -E -S EXCEDE\EXCEDE -d excede_Reporting -q"EXIT(exec isp_SetCrPw %1)"
Hi,
you would probably have to write your own application for this using
e.g. .NET. That should be quite easy, as the only thing would be to
have a argument parser in place, the rest is only some simply
SqlConnection and SqlCommand.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||That would be beyond my current abilities. I can write some very simple
scripts, and I have a mild understanding of some VB and .NET, But to write
my own code. Well I have no idea where to start. If you would like to help
and get me started I would greatly appreciate and enjoy the experience.
Thanks.
===============================================
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1171392808.470036.289720@.j27g2000cwj.googlegr oups.com...
> Hi,
> you would probably have to write your own application for this using
> e.g. .NET. That should be quite easy, as the only thing would be to
> have a argument parser in place, the rest is only some simply
> SqlConnection and SqlCommand.
>
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
|||Lets take that offline and write me an Email.
|||

Quote:

Originally Posted by WANNABE

That would be beyond my current abilities. I can write some very simple
scripts, and I have a mild understanding of some VB and .NET, But to write
my own code. Well I have no idea where to start. If you would like to help
and get me started I would greatly appreciate and enjoy the experience.
Thanks.
===============================================
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1171392808.470036.289720@.j27g2000cwj.googlegr oups.com...
> Hi,
> you would probably have to write your own application for this using
> e.g. .NET. That should be quite easy, as the only thing would be to
> have a argument parser in place, the rest is only some simply
> SqlConnection and SqlCommand.
>
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

I have written an OSQL like replacement in C#/.Net 2.0. I am
having a wee bit of an issue though. If the SQL file to be run
has dynamic SQL in it (building SQL strings and then executing
them) then the command will fail - always. I think it has
something to do with the nesting of the quotes. Got any ideas
on what would be causing something like this?
Thanks

OSQL

Creating a utility that will allow a user to run a query from a BAT file.
BUT the SQL client is not installed on all PCs. How can I modify this to
run, or what must I do in order for this to work from PCs with no SQL client
'
osql -E -S EXCEDE\EXCEDE -d excede_Reporting -q"EXIT(exec isp_SetCrPw %1)"Hi,
you would probably have to write your own application for this using
e.g. .NET. That should be quite easy, as the only thing would be to
have a argument parser in place, the rest is only some simply
SqlConnection and SqlCommand.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||That would be beyond my current abilities. I can write some very simple
scripts, and I have a mild understanding of some VB and .NET, But to write
my own code. Well I have no idea where to start. If you would like to help
and get me started I would greatly appreciate and enjoy the experience.
Thanks.
========================================
=======
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1171392808.470036.289720@.j27g2000cwj.googlegroups.com...
> Hi,
> you would probably have to write your own application for this using
> e.g. .NET. That should be quite easy, as the only thing would be to
> have a argument parser in place, the rest is only some simply
> SqlConnection and SqlCommand.
>
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||Lets take that offline and write me an Email.|||WANNABE;7269311 Wrote:[vbcol=seagreen]
> That would be beyond my current abilities. I can write some very
> simple
> scripts, and I have a mild understanding of some VB and .NET, But to
> write
> my own code. Well I have no idea where to start. If you would like to
> help
> and get me started I would greatly appreciate and enjoy the
> experience.
> Thanks.
> ========================================
=======
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1171392808.470036.289720@.j27g2000cwj.googlegroups.com...
I have written an OSQL like replacement in C#/.Net 2.0. I am
having a wee bit of an issue though. If the SQL file to be run
has dynamic SQL in it (building SQL strings and then executing
them) then the command will fail - always. I think it has
something to do with the nesting of the quotes. Got any ideas
on what would be causing something like this?
Thanks
escher4096

orphans user

I restored db from sql2000 to sql2005, i did fix the all
orphans users except SYS and DBO. How can i drop them from my db?
thanks
Read about sp_change_users_login in Books Online. Also Google for sp_help_revlogin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Viktor" <serguienkov@.hotmail.com> wrote in message news:OmbaV67yHHA.1188@.TK2MSFTNGP04.phx.gbl...
>I restored db from sql2000 to sql2005, i did fix the all
> orphans users except SYS and DBO. How can i drop them from my db?
> thanks
>

orphans user

I restored db from sql2000 to sql2005, i did fix the all
orphans users except SYS and DBO. How can i drop them from my db?
thanksRead about sp_change_users_login in Books Online. Also Google for sp_help_revlogin.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Viktor" <serguienkov@.hotmail.com> wrote in message news:OmbaV67yHHA.1188@.TK2MSFTNGP04.phx.gbl...
>I restored db from sql2000 to sql2005, i did fix the all
> orphans users except SYS and DBO. How can i drop them from my db?
> thanks
>

orphans user

I restored db from sql2000 to sql2005, i did fix the all
orphans users except SYS and DBO. How can i drop them from my db?
thanksRead about sp_change_users_login in Books Online. Also Google for sp_help_re
vlogin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Viktor" <serguienkov@.hotmail.com> wrote in message news:OmbaV67yHHA.1188@.TK2MSFTNGP04.phx.g
bl...
>I restored db from sql2000 to sql2005, i did fix the all
> orphans users except SYS and DBO. How can i drop them from my db?
> thanks
>

Orphaned user owns objects as dbo and cannot be dropped.

Hi,
Hope someone can give me a hint. I restored a database from the Development
SQL server to the production server. Normally, the database dbo user is
associated with sa login name, but this database's dbo user is associated
with NULL. The developer had dbo permission on this database in the Dev
environment. Now I am trying to drop his orphaned user name from this
database on the production server, but I got an error message: "The selected
user cannot be dropped because the user owns objects. However, I checked all
tables, views, and stored procedures they are all owned by the dbo not this
user. How can I get rid of this use name in this database?
Thank you in advance.
Jack.Jack
EXEC sp_change_users_login 'Report'
What do you get?
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:856F9A25-73B3-4668-8E8F-68E3CBD70C57@.microsoft.com...
> Hi,
> Hope someone can give me a hint. I restored a database from the
> Development
> SQL server to the production server. Normally, the database dbo user is
> associated with sa login name, but this database's dbo user is associated
> with NULL. The developer had dbo permission on this database in the Dev
> environment. Now I am trying to drop his orphaned user name from this
> database on the production server, but I got an error message: "The
> selected
> user cannot be dropped because the user owns objects. However, I checked
> all
> tables, views, and stored procedures they are all owned by the dbo not
> this
> user. How can I get rid of this use name in this database?
> Thank you in advance.
> Jack.|||> Normally, the database dbo user is
> associated with sa login name, but this database's dbo user is associated
> with NULL.
You can fix the database owner with:
USE MyDatabase
EXEC sp_changedbowner 'sa'

> "The selected
> user cannot be dropped because the user owns objects. However, I checked
> all
> tables, views, and stored procedures they are all owned by the dbo not
> this
> user. How can I get rid of this use name in this database?
I suspect this is a different problem than the database owner. The script
below will identify objects owned by the problem user:
SELECT name, xtype
FROM dbo.sysobjects
WHERE uid = USER_ID('DevUser')
Uri mentioned that you can use sp_change_users_login to identify problem
user/login mapping.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:856F9A25-73B3-4668-8E8F-68E3CBD70C57@.microsoft.com...
> Hi,
> Hope someone can give me a hint. I restored a database from the
> Development
> SQL server to the production server. Normally, the database dbo user is
> associated with sa login name, but this database's dbo user is associated
> with NULL. The developer had dbo permission on this database in the Dev
> environment. Now I am trying to drop his orphaned user name from this
> database on the production server, but I got an error message: "The
> selected
> user cannot be dropped because the user owns objects. However, I checked
> all
> tables, views, and stored procedures they are all owned by the dbo not
> this
> user. How can I get rid of this use name in this database?
> Thank you in advance.
> Jack.|||Thank you Dan and Uri,
I did run the sp_change_users_login 'Report' but the DevUser was not listed
as an orphaned user. When I run:
select name, xtype from dbo.sysobjects where uid = USER_ID('DevUser')
I got following result:
Name xtype
current_date D
empty_string D
zero D
Those three object are default constraints. I don't how this could happen? I
tried to use sp_changeobjectowner to change the owership on these objects,
but got the following message:
Object 'zero' does not exist or is not a valid object for this operation.
So I had to directly update the dbo.sysobjects system table to chane the uid
to 1 for these three objects, then I dropped the DevUser successfully. What
I
did might not be a safe way to solve this problem. Do you have any other
better ways to get this problem corrected?
Thank you so much for your help.
Jack
"Dan Guzman" wrote:

> You can fix the database owner with:
> USE MyDatabase
> EXEC sp_changedbowner 'sa'
>
> I suspect this is a different problem than the database owner. The script
> below will identify objects owned by the problem user:
> SELECT name, xtype
> FROM dbo.sysobjects
> WHERE uid = USER_ID('DevUser')
> Uri mentioned that you can use sp_change_users_login to identify problem
> user/login mapping.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jack" <Jack@.discussions.microsoft.com> wrote in message
> news:856F9A25-73B3-4668-8E8F-68E3CBD70C57@.microsoft.com...
>
>|||I haven't run into orphaned default constraints before. You might try DBCC
CHECKCATALOG to see if turns up anything else.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:A1CCBE29-5946-4235-A8FC-C53D7058853F@.microsoft.com...[vbcol=seagreen]
> Thank you Dan and Uri,
> I did run the sp_change_users_login 'Report' but the DevUser was not
> listed
> as an orphaned user. When I run:
> select name, xtype from dbo.sysobjects where uid = USER_ID('DevUser')
> I got following result:
> Name xtype
> current_date D
> empty_string D
> zero D
> Those three object are default constraints. I don't how this could happen?
> I
> tried to use sp_changeobjectowner to change the owership on these objects,
> but got the following message:
> Object 'zero' does not exist or is not a valid object for this operation.
> So I had to directly update the dbo.sysobjects system table to chane the
> uid
> to 1 for these three objects, then I dropped the DevUser successfully.
> What I
> did might not be a safe way to solve this problem. Do you have any other
> better ways to get this problem corrected?
> Thank you so much for your help.
> Jack
>
> "Dan Guzman" wrote:
>