Hi Everyone.
I have 150 SQL servers (2000 MSDE). They all run using various domain accounts as their service logins. Is there an automated way to find out those service logins? Maybe a query I could run on each server?
I really do not want to go to each of those 150 servers and look at their properties manualy! :S
Any help would be greatly appreciated! Thank you.Something around the master.dbo.sysusers and/or master.dbo.syslogins
table(s) ?|||I think that you want the startname value from this (http://www.microsoft.com/technet/scriptcenter/scripts/os/services/ossvvb08.mspx) script.
-PatP|||I think that you want the startname value from this (http://www.microsoft.com/technet/scriptcenter/scripts/os/services/ossvvb08.mspx) script.
-PatP
Yeah, I would think that WMI would be your best bet. Dump the list of servers into a text file (or XML). Open that file using the File system object and spin through (using oFile.ReadLine). Then feed the string value (the computer name) into a function that returns the name of of the service account you are looking for. Dump that into a separate text file (or a database).
Regards,
hmscott|||Thank you everyone for responding. I got an answer at experts-exchange. The following query will return the service account:
declare @.rc int,
@.dir nvarchar(4000)
exec @.rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'System\CurrentControlSet\S ervices\MSSQLServer\',N'ObjectName', @.dir output, 'no_output'
select @.dir
I've modified it put the result in the central table and then ran it through a SQL loop. Worked perfectly! :D
WMI would have worked as well, but this solution took just 10 min to implement.
P.S. Had no idea that something like xp_instance_regread existed. Is too much to hope that sql server 2005 will have better documentation? :-)|||That Transact-SQL code will work nicely as long as you only need the default instance of SQL 2000.
-PatP|||That Transact-SQL code will work nicely as long as you only need the default instance of SQL 2000.
-PatP
Good point, Pat. For named instances the command will need to be modified:
declare @.rc int,
@.dir nvarchar(4000)
exec @.rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'System\CurrentControlSet\S ervices\MSSQL$Your_Instance_Name\',N'ObjectName', @.dir output, 'no_output'
select @.dir
Showing posts with label msde. Show all posts
Showing posts with label msde. Show all posts
Monday, March 19, 2012
Friday, March 9, 2012
How to get query result in XML format using osql.exe (command line)
Hi.
I'm using MSDE 2000. Is there a way to get a query result in a 'readabe'
XML format from command line?
I found the option 'FOR XML', however this produced something that is
not really XML. E.g.:
SELECT * FROM dbo.master FOR XML
Thanks and best regards,
Dezo
You can execute SQL queries to return results as XML rather than standard
rowsets. These queries can be executed directly or from within stored
procedures. To retrieve results directly, you use the FOR XML clause of the
SELECT statement, and within the FOR XML clause you specify an XML mode:
RAW, AUTO, or EXPLICIT.
For example, this SELECT statement retrieves information from Customers and
Orders table in the Northwind database. This query specifies the AUTO mode
in the FOR XML clause:
SELECT Customers.CustomerID, ContactName, CompanyName,
Orders.CustomerID, OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
AND (Customers.CustomerID = N'ALFKI'
OR Customers.CustomerID = N'XYZAA')
ORDER BY Customers.CustomerID
FOR XML AUTO
The output will be in the xml format .
This posting is provided "AS IS" with no warranties, and confers no rights.
I'm using MSDE 2000. Is there a way to get a query result in a 'readabe'
XML format from command line?
I found the option 'FOR XML', however this produced something that is
not really XML. E.g.:
SELECT * FROM dbo.master FOR XML
Thanks and best regards,
Dezo
You can execute SQL queries to return results as XML rather than standard
rowsets. These queries can be executed directly or from within stored
procedures. To retrieve results directly, you use the FOR XML clause of the
SELECT statement, and within the FOR XML clause you specify an XML mode:
RAW, AUTO, or EXPLICIT.
For example, this SELECT statement retrieves information from Customers and
Orders table in the Northwind database. This query specifies the AUTO mode
in the FOR XML clause:
SELECT Customers.CustomerID, ContactName, CompanyName,
Orders.CustomerID, OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
AND (Customers.CustomerID = N'ALFKI'
OR Customers.CustomerID = N'XYZAA')
ORDER BY Customers.CustomerID
FOR XML AUTO
The output will be in the xml format .
This posting is provided "AS IS" with no warranties, and confers no rights.
Wednesday, March 7, 2012
How to get ODBC working with MSDE
Hi,
yes thanks .. but I did figure that out by myself.
SQLServer is running, I am able to osql on it ...
but ODBC refuses connectivity.
Maybe I just forgot something else.
>please have a look at
>http://support.microsoft.com/default...&Product=sql2k
You could checkout if your network protocols are on or not. OSQL may be
using shared memory connection here so it may be working properly.
KB article http://support.microsoft.com/?id=827204
yes thanks .. but I did figure that out by myself.
SQLServer is running, I am able to osql on it ...
but ODBC refuses connectivity.
Maybe I just forgot something else.
>please have a look at
>http://support.microsoft.com/default...&Product=sql2k
You could checkout if your network protocols are on or not. OSQL may be
using shared memory connection here so it may be working properly.
KB article http://support.microsoft.com/?id=827204
Sunday, February 19, 2012
How to get from smalldatetime -field only date?
Hi!
I'm using MSDE 2000 SP4. I need to get only date from smalldatetime
using SQL statement like "SELECT MyDate FROM MyTable.".
Like this way I'll get MyDate as "27.11.2005 0:00:00" when using finnish
regional settings, but how to get just "27.11.2005" without that empty time?
I think this is quite easy to solve.
Thanks in advance!
Mika
You have to use the CONVERT Function to dispaly it in the proper way:
Select CONVERT(VARCHAR(10),'27.11.2005 0:00:00',103)
HTH, jens Suessmeyer.
|||Jens kirjoitti:
> You have to use the CONVERT Function to dispaly it in the proper way:
> Select CONVERT(VARCHAR(10),'27.11.2005 0:00:00',103)
> HTH, jens Suessmeyer.
>
Nice, thanks Jens!
Is there a link where those different form numbers like 103 is
described? Looks like 104 is like finnish form of date, which I just
guessed.
|||Look int he BOL which is delivered within SQL Server or use the online
BOL:
http://msdn.microsoft.com/library/en...ca-co_2f3o.asp
HTH, Jens Suessmeyer.
I'm using MSDE 2000 SP4. I need to get only date from smalldatetime
using SQL statement like "SELECT MyDate FROM MyTable.".
Like this way I'll get MyDate as "27.11.2005 0:00:00" when using finnish
regional settings, but how to get just "27.11.2005" without that empty time?
I think this is quite easy to solve.
Thanks in advance!
Mika
You have to use the CONVERT Function to dispaly it in the proper way:
Select CONVERT(VARCHAR(10),'27.11.2005 0:00:00',103)
HTH, jens Suessmeyer.
|||Jens kirjoitti:
> You have to use the CONVERT Function to dispaly it in the proper way:
> Select CONVERT(VARCHAR(10),'27.11.2005 0:00:00',103)
> HTH, jens Suessmeyer.
>
Nice, thanks Jens!
Is there a link where those different form numbers like 103 is
described? Looks like 104 is like finnish form of date, which I just
guessed.
|||Look int he BOL which is delivered within SQL Server or use the online
BOL:
http://msdn.microsoft.com/library/en...ca-co_2f3o.asp
HTH, Jens Suessmeyer.
Subscribe to:
Posts (Atom)