Determine SQL Server service account via script

Is there SQL code that will report the name of the Windows account that the
SQL Server service is using? I'd be doing this using a login in the sysadmi
n
role.
I've searched BOL, the web, and newsgroups. But I haven't found a way.
Thanks
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.comJon
DECLARE @.serviceaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQ
LSERVER',
N'ObjectName',
@.ServiceAccount OUTPUT,
N'no_output'
SELECT @.Serviceaccount
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:17635F3D-6CF1-4ECA-941D-DAB9652D81D7@.microsoft.com...
> Is there SQL code that will report the name of the Windows account that
> the
> SQL Server service is using? I'd be doing this using a login in the
> sysadmin
> role.
> I've searched BOL, the web, and newsgroups. But I haven't found a way.
> Thanks
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.comsql

Determine Replication Folder or Data Folder via SQL?

I have an application that has to do the following
1. Disable Replication
2. Run Database Changes Script
3. Enable Replication again.
I have the scripts to enable and/or disable replication, but I was wondering
if there was anyway to retrieve the data, log, and replication folder
directories via sql so that I can use them in my replication sql script
without having to hard code these values.
L Anthony Johnson
Newbie
sp_browsesnapshotfolder @.publication = 'PublicationName' will tell you the
snapshot folder name (as a UNC).
sp_helpdb 'DBName' will tell you the name of the db.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"L Anthony Johnson" <ppsyco2@.nospamm4m3bellsouth.net> wrote in message
news:%23BsT8$McEHA.3016@.tk2msftngp13.phx.gbl...
> I have an application that has to do the following
> 1. Disable Replication
> 2. Run Database Changes Script
> 3. Enable Replication again.
> I have the scripts to enable and/or disable replication, but I was
wondering
> if there was anyway to retrieve the data, log, and replication folder
> directories via sql so that I can use them in my replication sql script
> without having to hard code these values.
> --
> L Anthony Johnson
> Newbie
>

detect connectable db servers

Hello,

how I can detect via code (C++, C#) which sql servers like oracle, mssql,
mysql are close to me and connectable within one or two second (timeout to
wait for answers should be variable)?

Thank you.

regards
MarkMark wrote:
> Hello,
> how I can detect via code (C++, C#) which sql servers like oracle, mssql,
> mysql are close to me and connectable within one or two second (timeout to
> wait for answers should be variable)?
> Thank you.
> regards
> Mark

I don't think it's possible. You should already know the names of the servers
you have permission to connect to.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@.attglobal.net
==================|||On Tue, 16 May 2006 19:06:07 +0200, I waved a wand and this message
magically appeared from Mark:

> how I can detect via code (C++, C#) which sql servers like oracle,
> mssql, mysql are close to me and connectable within one or two second
> (timeout to wait for answers should be variable)?

I know how to, but I will not tell you. It is against the law to access
computers if you are not authorised to do so.
--
http://www.munted.org.uk

Take a nap, it saves lives.|||you are all misguided... whether or not it is against the law to access other computers is a stupid argument... hope this helps

the framework (2.0) provides several methods for detecting, but this one is specialized for enumerating sql servers.

http://msdn2.microsoft.com/en-us/library/system.data.sql.sqldatasourceenumerator.getdatasou rces.aspx

Detach/Attach Via Management Studio

I really can't believe how difficult this is.
Open SQL Server Management Studio, browse to the databases, right-click the
one to detach, select Tasks>Detach... Check the Drop Connections checkbox,
click OK... Bang! Failed.
"The Database is not accessable..." pops up during the process. Click on the
OK.
"Cannot detach the database 'test' because it is currently in use.
(Microsoft SQL Server, Error: 3703)"
Hold on... Didn't I ask it to drop the connections? So why is it still in
use?
Look at the database, it's in Single User mode. OK. Let's get it back to
normal... Right-click, select properties... Bang! Error.
"Cannot show requested dialog."
"Database 'test' is already open and can only have one user at a time.
(Microsoft SQL Server, Error: 924)"
Who the heck has it open? Check the Activity Monitor:
A suspended delete command through the web application db user...
"(@.p2 int)BEGIN CONVERSATION TIMER ('37238b35-6439-db11-934c-00137260bfc2')
TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name,
conversation_handle, cast(message_body AS XML) as message_body from
[SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]), TIMEOUT
@.p2;"
Unfortunately, I can't seem to kill the damn process. It just refuses to go
away. And the developers have no idea what it's for, so it must be some .NET
2.0 assembly thing.
Anyone know how to deal with this apart from shutting down the web
application server?Hi,
Open the query window and try this script...
ALTER DATABASE <DBNAME> SET SINGLE_USER WITH Rollback Immediate
GO
SP_Detach_db <dbname>
Thanks
Hari
SQL Server MVP
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||The process is the server side of the Dependency client event that they use
to get a notification when something changes the data for a specified query.
It should be killable unless the client is restarting it. You may need to
stop the client app to get it to go away.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||There was a Service Broker for the client running on the database server
that wouldn't let the process finish. Rebooting the web application server
allowed me to detach/attach, but I was also able to do the same by removing
and adding the broker.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OL%23bT2XzGHA.1536@.TK2MSFTNGP02.phx.gbl...
> The process is the server side of the Dependency client event that they
> use to get a notification when something changes the data for a specified
> query. It should be killable unless the client is restarting it. You may
> need to stop the client app to get it to go away.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>>I really can't believe how difficult this is.
>> Open SQL Server Management Studio, browse to the databases, right-click
>> the one to detach, select Tasks>Detach... Check the Drop Connections
>> checkbox, click OK... Bang! Failed.
>> "The Database is not accessable..." pops up during the process. Click on
>> the OK.
>> "Cannot detach the database 'test' because it is currently in use.
>> (Microsoft SQL Server, Error: 3703)"
>> Hold on... Didn't I ask it to drop the connections? So why is it still in
>> use?
>> Look at the database, it's in Single User mode. OK. Let's get it back to
>> normal... Right-click, select properties... Bang! Error.
>> "Cannot show requested dialog."
>> "Database 'test' is already open and can only have one user at a time.
>> (Microsoft SQL Server, Error: 924)"
>> Who the heck has it open? Check the Activity Monitor:
>> A suspended delete command through the web application db user...
>> "(@.p2 int)BEGIN CONVERSATION TIMER
>> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
>> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
>> as message_body from
>> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
>> TIMEOUT @.p2;"
>> Unfortunately, I can't seem to kill the damn process. It just refuses to
>> go away. And the developers have no idea what it's for, so it must be
>> some .NET 2.0 assembly thing.
>> Anyone know how to deal with this apart from shutting down the web
>> application server?
>>
>

Detach/Attach Via Management Studio

I really can't believe how difficult this is.
Open SQL Server Management Studio, browse to the databases, right-click the
one to detach, select Tasks>Detach... Check the Drop Connections checkbox,
click OK... Bang! Failed.
"The Database is not accessable..." pops up during the process. Click on the
OK.
"Cannot detach the database 'test' because it is currently in use.
(Microsoft SQL Server, Error: 3703)"
Hold on... Didn't I ask it to drop the connections? So why is it still in
use?
Look at the database, it's in Single User mode. OK. Let's get it back to
normal... Right-click, select properties... Bang! Error.
"Cannot show requested dialog."
"Database 'test' is already open and can only have one user at a time.
(Microsoft SQL Server, Error: 924)"
Who the heck has it open? Check the Activity Monitor:
A suspended delete command through the web application db user...
"(@.p2 int)BEGIN CONVERSATION TIMER ('37238b35-6439-db11-934c-00137260bfc2')
TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name,
conversation_handle, cast(message_body AS XML) as message_body from
[SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]), TIM
EOUT
@.p2;"
Unfortunately, I can't seem to kill the damn process. It just refuses to go
away. And the developers have no idea what it's for, so it must be some .NET
2.0 assembly thing.
Anyone know how to deal with this apart from shutting down the web
application server?Hi,
Open the query window and try this script...
ALTER DATABASE <DBNAME> SET SINGLE_USER WITH Rollback Immediate
GO
SP_Detach_db <dbname>
Thanks
Hari
SQL Server MVP
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||The process is the server side of the Dependency client event that they use
to get a notification when something changes the data for a specified query.
It should be killable unless the client is restarting it. You may need to
stop the client app to get it to go away.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||There was a Service Broker for the client running on the database server
that wouldn't let the process finish. Rebooting the web application server
allowed me to detach/attach, but I was also able to do the same by removing
and adding the broker.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OL%23bT2XzGHA.1536@.TK2MSFTNGP02.phx.gbl...
> The process is the server side of the Dependency client event that they
> use to get a notification when something changes the data for a specified
> query. It should be killable unless the client is restarting it. You may
> need to stop the client app to get it to go away.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>

Detach replicated DB?

Trying to detach a DB from the publisher/distributer via EM from another PC
on the network. The detach box has the message: ATTENTION: The database
cannot be detached while it is being replicated. And the OK button is
disabled. I took this as you cannot detach during replication. But it is
not being replicated now. Does this mean a DB set up as a publisher can't be
detached at all?
Thanks,
Steve
After reading further, it appears that I can't do this. So I have deleted
the publications. But I still get the ATTENTION message and can't detach.
"SteveInBeloit" wrote:

> Trying to detach a DB from the publisher/distributer via EM from another PC
> on the network. The detach box has the message: ATTENTION: The database
> cannot be detached while it is being replicated. And the OK button is
> disabled. I took this as you cannot detach during replication. But it is
> not being replicated now. Does this mean a DB set up as a publisher can't be
> detached at all?
> Thanks,
> Steve
|||From another post I read,
You will be unable to detach replicated databases. You have to script out
the publications, edit them for the new server, remove all job names, and
then drop the publications, unpublish the databases and detach them.
I have deleted the publications, but what is mean't by unpublish the database?
"SteveInBeloit" wrote:
[vbcol=seagreen]
> After reading further, it appears that I can't do this. So I have deleted
> the publications. But I still get the ATTENTION message and can't detach.
> "SteveInBeloit" wrote:
|||In EM, connect with your SQL Server, select Tools, Replication, Configure
Distributor, Publishers and subscribers, Click on the Published databases
tab, and uncheck the check marks for transactional or merge if the database
is published.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:182E9763-9D45-4EB7-8C60-AB9F7F4F7ABD@.microsoft.com...
> From another post I read,
> You will be unable to detach replicated databases. You have to script out
> the publications, edit them for the new server, remove all job names, and
> then drop the publications, unpublish the databases and detach them.
> I have deleted the publications, but what is mean't by unpublish the
database?[vbcol=seagreen]
>
> "SteveInBeloit" wrote:
deleted[vbcol=seagreen]
detach.[vbcol=seagreen]
another PC[vbcol=seagreen]
database[vbcol=seagreen]
it is[vbcol=seagreen]
can't be[vbcol=seagreen]
|||Thank you!
"Hilary Cotter" wrote:

> In EM, connect with your SQL Server, select Tools, Replication, Configure
> Distributor, Publishers and subscribers, Click on the Published databases
> tab, and uncheck the check marks for transactional or merge if the database
> is published.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
> news:182E9763-9D45-4EB7-8C60-AB9F7F4F7ABD@.microsoft.com...
> database?
> deleted
> detach.
> another PC
> database
> it is
> can't be
>
>
|||Hilary,
If you delete the publisher and the subscriber to detach the published
database, how do you reinstae the publisher and the subscriber? I hope I do
not have to do the replication all over from the beginning.
Thanks,
George Gopie
"Hilary Cotter" wrote:

> In EM, connect with your SQL Server, select Tools, Replication, Configure
> Distributor, Publishers and subscribers, Click on the Published databases
> tab, and uncheck the check marks for transactional or merge if the database
> is published.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
> news:182E9763-9D45-4EB7-8C60-AB9F7F4F7ABD@.microsoft.com...
> database?
> deleted
> detach.
> another PC
> database
> it is
> can't be
>
>

Detach and Attach functions in SQL Server 2005

Hi,

I'm trying to port my ASP.NET web application to the production system.

I'm connection to the SQL Server 2005 instance on my hosting server via CTP. I've uploaded the .mdf and .ldf files of my DB via FTP to the hosting server, and then tried to attach using this command:

use master gosp_attach_db'tgp','F:\webspace\disk20\db\TGP.mdf','F:\webspace\disk20\db\TGP_log.ldf' go

but then there is an error (obviously) stating that I don't have permissions to create a database in database master.

I must admit that I'm prettycluelessin this area. My hostingservicesalready created a "place holder" for my database (tgp), but I don't know how to proceed from here in order attach the database files in the production environment. Is this something I can do myself, or must I involve the hosting services?

Thanks,

Alon

sp_attach_db requires the same permissions as CREATE DATABASE - so you're likely not going to be able to do this (if so, let me know who your host is - I could use the free/extra space they'd let me set up *grin*).

You'll either need to contact them and have them hook up your DB, or look into using user-instance/attachable SQL Express functionality.|||

You have two options just backup your database and use management studio to restore your database on the host SQL Server after you have registered the host SQL Server in your management studio. In the backup and restore wizard choose the restore from device option. The other option is try the thread below for Attach database code modify it for your needs and use it. Hope this helps.

http://forums.asp.net/thread/981274.aspx

|||

Thak you for your reply.

I tried to backup/restore, but when I tried to point to the location of the backup file (on the remote server), got the error message that I'm not authorized... I've just sent a request from the hosting firm to handle this.

I have a general question: what are the guidelines when moving from the test/development system to the production system? I couldn't find any article summarizing the process.

Thank you,

Alon

|||You should only use a company that gives you access to management studio so you can use the backup and restore wizard because if you don't backup your database they will charge you to back it up. Here in the US most hosting company will give you the access. Hope this helps.|||

Yep, this helps

Alon

Design For Loading Data Without Knowlged of Datatype or Column Count

I am loading data from an external source into SQL Server via ASP.NET/C#. The problem is that I do not necessarily know the data types of each column coming in, perhaps until a user tells the application, which might not occur until after the data is loaded. Also, I cannot anticipate the number of columns coming in. What would table design look like?

Would you use a large table with enough columns (e.g. Column1, Column2, etc.) reasonable enough to accomodate all the columns that the source might have (32?), and use nchar as the datatype with the plan to convert/cast when I use the data? Isn't the cast kind of expensive?

Does this make sense? Surely other foplks have run into this...

My thanks!

If you're doing what I think you're doing, you might want to use an EAV (Entity/Attribute/Value) table to store your data. The attribute table stores the column definitions. The entity table store the "row id"s. The value table stores the actual values and is of the form

Entity_id int

Attribute_id int

Value (string/whatever)

You can have multiple value tables for different data types, but I find that it's more trouble than it's worth. I've used this on several projects where the data being captured is set by the users at run time.