OSQL output

Hi,
I run a script on all of our SQL Servers (+300 servers) on a regular basis
to collect information about different instances, version, port and so on
for each SQL Server using a loop than runs OSQL and passes a query to it for
each member server. The passed query then collects this information on each
server and saves the results in main server using OPENROWSET.
Due to different SQL Server and MDAC versions on each member SQL server,
sometimes OPENROWSET fails to saves its results.
Is there a way to get the output of the OSQL as tabular values so I can pass
the query (without OPENROWSET inside the query) using OSQL, get the results
in tabular format (like a table) and then save the results in main table.
This way, I will avoid using costly OPENROWSET.
My main server is SQL Server 2000 SP4 and we have different range of SQL
Servers and SPs.
Thanks> Is there a way to get the output of the OSQL as tabular values so I can pass
> the query (without OPENROWSET inside the query) using OSQL, get the results
> in tabular format (like a table) and then save the results in main table.
The output of osql.exe is text. You typically need an intermediate
program/script to parse it and reformat it before feeding the result into a
table(s).
Linchi
"Alex" wrote:
> Hi,
> I run a script on all of our SQL Servers (+300 servers) on a regular basis
> to collect information about different instances, version, port and so on
> for each SQL Server using a loop than runs OSQL and passes a query to it for
> each member server. The passed query then collects this information on each
> server and saves the results in main server using OPENROWSET.
> Due to different SQL Server and MDAC versions on each member SQL server,
> sometimes OPENROWSET fails to saves its results.
> Is there a way to get the output of the OSQL as tabular values so I can pass
> the query (without OPENROWSET inside the query) using OSQL, get the results
> in tabular format (like a table) and then save the results in main table.
> This way, I will avoid using costly OPENROWSET.
> My main server is SQL Server 2000 SP4 and we have different range of SQL
> Servers and SPs.
> Thanks
>
>

OSQL -L

Hi All,
Can anyone please tell me if we issue "OSQL -L " at command prompt, does it
lists all the SQL Servers in the network or just windows servers within the
network?
Thanks,
VMIt shows you all broadcasted SQL Server instances over the net, whereas the
instances are hided for discovery.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"VM" <VM@.discussions.microsoft.com> schrieb im Newsbeitrag
news:695DABDF-AE47-430A-9AF8-1E495BBF6C7D@.microsoft.com...
> Hi All,
> Can anyone please tell me if we issue "OSQL -L " at command prompt, does
> it
> lists all the SQL Servers in the network or just windows servers within
> the
> network?
> Thanks,
> VM|||> Can anyone please tell me if we issue "OSQL -L " at command prompt, does it
> lists all the SQL Servers in the network or just windows servers within the
> network?
SQL Servers. But be carefull, because if you create an alias using Client
Network Utility, this command will list it even if the alias is not
associated with a physical server.
AMB
"VM" wrote:
> Hi All,
> Can anyone please tell me if we issue "OSQL -L " at command prompt, does it
> lists all the SQL Servers in the network or just windows servers within the
> network?
> Thanks,
> VM

OSQL -L

Hi All,
Can anyone please tell me if we issue "OSQL -L " at command prompt, does it
lists all the SQL Servers in the network or just windows servers within the
network?
Thanks,
VM
It shows you all broadcasted SQL Server instances over the net, whereas the
instances are hided for discovery.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"VM" <VM@.discussions.microsoft.com> schrieb im Newsbeitrag
news:695DABDF-AE47-430A-9AF8-1E495BBF6C7D@.microsoft.com...
> Hi All,
> Can anyone please tell me if we issue "OSQL -L " at command prompt, does
> it
> lists all the SQL Servers in the network or just windows servers within
> the
> network?
> Thanks,
> VM
|||> Can anyone please tell me if we issue "OSQL -L " at command prompt, does it
> lists all the SQL Servers in the network or just windows servers within the
> network?
SQL Servers. But be carefull, because if you create an alias using Client
Network Utility, this command will list it even if the alias is not
associated with a physical server.
AMB
"VM" wrote:

> Hi All,
> Can anyone please tell me if we issue "OSQL -L " at command prompt, does it
> lists all the SQL Servers in the network or just windows servers within the
> network?
> Thanks,
> VM

osql -L

Can anyone suggest why two client PCs on the same network report different
lists of servers using osql -L ?
Nick.
BOL says the -L option 'Lists the locally configured servers and the names
of the servers broadcasting on the network.'
So... it could be that they really are on different subnets and yuo don't
realize it. But 'locally configured' servers is the more likely answer.
-L will show all server aliases hat are defined on the client using the
Client Network Utility. You probably have different lists there...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Nick Stephens" <NickStephens@.discussions.microsoft.com> wrote in message
news:7C1AC5D3-F374-4BFA-B220-90D97DD253F4@.microsoft.com...
> Can anyone suggest why two client PCs on the same network report different
> lists of servers using osql -L ?
> Nick.
|||My colleague and I had also discussed the possibility of different subnets
being an issue, but didn't have the tehcnical knowledge to sound out our
ideas so we moved on and in fact checked out the configs via the Client
Network Utility.
The only difference that we found was a single alias on his machine whereas
on mine the list is empty and yet there are 49 servers to be found. Maybe
there's a different list of which I'm unaware ?
Inspired by a reply to a similar post I knocked up a quick VB6 app to use
DMO and list the servers. Unsurprisingly, I got exactly the same list as with
osql -L.
However, several entries in my list differ from that when we use my
colleague's machine.
Still, at your suggestion I'll take a closer look at the configs, Biran -
many thanks for your help.
Regards, Nick.
"Brian Moran" wrote:

>
> BOL says the -L option 'Lists the locally configured servers and the names
> of the servers broadcasting on the network.'
> So... it could be that they really are on different subnets and yuo don't
> realize it. But 'locally configured' servers is the more likely answer.
> -L will show all server aliases hat are defined on the client using the
> Client Network Utility. You probably have different lists there...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Nick Stephens" <NickStephens@.discussions.microsoft.com> wrote in message
> news:7C1AC5D3-F374-4BFA-B220-90D97DD253F4@.microsoft.com...
>
>
|||Brian, further to my initial reply to your post, we have found that it is
indeed a subnet thang. Our machines are on different subnets.
Thanks again.
Regards, Nick.
"Brian Moran" wrote:

>
> BOL says the -L option 'Lists the locally configured servers and the names
> of the servers broadcasting on the network.'
> So... it could be that they really are on different subnets and yuo don't
> realize it. But 'locally configured' servers is the more likely answer.
> -L will show all server aliases hat are defined on the client using the
> Client Network Utility. You probably have different lists there...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Nick Stephens" <NickStephens@.discussions.microsoft.com> wrote in message
> news:7C1AC5D3-F374-4BFA-B220-90D97DD253F4@.microsoft.com...
>
>
|||You can get different results at different times from the same PC as
well due to server responses not coming back in time. OSQL -L is based
on UDP network broadcasts. There are different factors that play into
what you get back for results. You can find the issues listed at:
http://www.sqldev.net/misc/ListSQLSvr.htm
-Sue
On Thu, 4 Nov 2004 08:13:03 -0800, "Nick Stephens"
<NickStephens@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Brian, further to my initial reply to your post, we have found that it is
>indeed a subnet thang. Our machines are on different subnets.
>Thanks again.
>Regards, Nick.
>"Brian Moran" wrote:
|||Many thanks, Sue !
Nick.
"Sue Hoegemeier" wrote:

> You can get different results at different times from the same PC as
> well due to server responses not coming back in time. OSQL -L is based
> on UDP network broadcasts. There are different factors that play into
> what you get back for results. You can find the issues listed at:
> http://www.sqldev.net/misc/ListSQLSvr.htm
> -Sue
> On Thu, 4 Nov 2004 08:13:03 -0800, "Nick Stephens"
> <NickStephens@.discussions.microsoft.com> wrote:
>
>

OSQL -L

Hi All,
Can anyone please tell me if we issue "OSQL -L " at command prompt, does it
lists all the SQL Servers in the network or just windows servers within the
network?
Thanks,
VMIt shows you all broadcasted SQL Server instances over the net, whereas the
instances are hided for discovery.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"VM" <VM@.discussions.microsoft.com> schrieb im Newsbeitrag
news:695DABDF-AE47-430A-9AF8-1E495BBF6C7D@.microsoft.com...
> Hi All,
> Can anyone please tell me if we issue "OSQL -L " at command prompt, does
> it
> lists all the SQL Servers in the network or just windows servers within
> the
> network?
> Thanks,
> VM|||> Can anyone please tell me if we issue "OSQL -L " at command prompt, does it">
> lists all the SQL Servers in the network or just windows servers within th
e
> network?
SQL Servers. But be carefull, because if you create an alias using Client
Network Utility, this command will list it even if the alias is not
associated with a physical server.
AMB
"VM" wrote:

> Hi All,
> Can anyone please tell me if we issue "OSQL -L " at command prompt, does i
t
> lists all the SQL Servers in the network or just windows servers within th
e
> network?
> Thanks,
> VM

OS error 53 for SQL servers VPN replication?

I've got this error message when trying to create a subscriber from a
publisher through VPN in a non-trusted domain, I am using SQL server
authentication:
The schema script '\\MERCURY\G$\Program Files\Microsoft SQL
Server\MSSQL$MERCURY\ReplData\unc\MERCUR
Y$MERCURY_Northwind_NorthwindMercury
Pub1\20040221225704\snapshot.pre' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
----
--
The process could not read file '\\MERCURY\G$\Program Files\Microsoft SQL
Server\MSSQL$MERCURY\ReplData\unc\MERCUR
Y$MERCURY_Northwind_NorthwindMercury
Pub1\20040221225704\snapshot.pre' due to OS error 53.
(Source: ECSWEB (Agent); Error number: 0)
----
--
The network path was not found.
(Source: (OS); Error number: 53)
What is OS error 53 and how can it be fixed?This error is generally caused by the fact that the SQL Server Agent
startup account on either the publisher or subscriber is not a local
administrator on one or both of the machines.
The default location for the snapshot folder is an administrative share and
as such the SQL Server Agent acount must be a local adminsitrator to access
it. From the error I am going to presume that you are creating a pull
subscription so the SQL Server Agent startup account at the subscriber must
be a local admin on the publisher. You can verify if this is the problem by
logging on to the subscriber using the SQL Server Agent startup account and
try to map a drive to the snap shot folder on the publisher:
\\MERCURY\G$\
If that fails with the same error then you have your reason for the failure.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Thanks Rand,
Yes that's right the two SQL servers are in different
non-trusted domains connected through VPN. The Distributor and Publisher SQL
Server is in the NARC domain and Agent login account as NARC\Administrator.
And the subscriber is in the HOT domain and Agent Login account is
HOT\Administrator. No trusted can be established between them. What must I
do to achieved successful pull subscription? How do I allow permissions in
the NARC domain for HOT\Administrator if there's no trusted' What about
independent SQL server logins to access shares in NARC domain(How do I set
it up if possible)?
Thanks
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:fjJVvoV$DHA.3712@.cpmsftngxa06.phx.gbl...
> This error is generally caused by the fact that the SQL Server Agent
> startup account on either the publisher or subscriber is not a local
> administrator on one or both of the machines.
> The default location for the snapshot folder is an administrative share
and
> as such the SQL Server Agent acount must be a local adminsitrator to
access
> it. From the error I am going to presume that you are creating a pull
> subscription so the SQL Server Agent startup account at the subscriber
must
> be a local admin on the publisher. You can verify if this is the problem
by
> logging on to the subscriber using the SQL Server Agent startup account
and
> try to map a drive to the snap shot folder on the publisher:
> \\MERCURY\G$\
> If that fails with the same error then you have your reason for the
failure.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||Joe,
I haven't got Rand's post available here, but I would advise to either use
SQL security or use pass-through authentication. Both are described in the
link in my earlier post.
Regards,
Paul Ibison
"Joe Mine" <huytuanattpgdotcomdotau> wrote in message
news:u9ThB04$DHA.2216@.TK2MSFTNGP10.phx.gbl...
> Thanks Rand,
> Yes that's right the two SQL servers are in different
> non-trusted domains connected through VPN. The Distributor and Publisher
SQL
> Server is in the NARC domain and Agent login account as
NARC\Administrator.
> And the subscriber is in the HOT domain and Agent Login account is
> HOT\Administrator. No trusted can be established between them. What must I
> do to achieved successful pull subscription? How do I allow permissions
in
> the NARC domain for HOT\Administrator if there's no trusted' What about
> independent SQL server logins to access shares in NARC domain(How do I set
> it up if possible)?
> Thanks
>
> "Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
> news:fjJVvoV$DHA.3712@.cpmsftngxa06.phx.gbl...
> and
> access
> must
> by
> and
> failure.
rights.
>|||Hi Paul,
Can you please give me the links again. I cannot locate your
previous post. The only problem I am having is I cannot from the Subcriber
SQL Server access the Snapshot Folder in the Distributor SQL Server because
this is two different non-trusted domains.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eLNYLS8$DHA.2212@.TK2MSFTNGP10.phx.gbl...
> Joe,
> I haven't got Rand's post available here, but I would advise to either use
> SQL security or use pass-through authentication. Both are described in the
> link in my earlier post.
> Regards,
> Paul Ibison
>
> "Joe Mine" <huytuanattpgdotcomdotau> wrote in message
> news:u9ThB04$DHA.2216@.TK2MSFTNGP10.phx.gbl...
different
> SQL
> NARC\Administrator.
I
permissions
> in
about
set
share
problem
account
> rights.
>|||Try this article:
321822 HOW TO: Replicate Between Computers Running SQL Server in
Non-Trusted http://support.microsoft.com/?id=321822
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Orphaned SQL Logins on the Mirror

Hi there,

I’ve recently set-up database mirroring between two servers in the same domain: DMZSQL01 and DMZSQL02 with a witness of DMZSQL03.

The mirroring as all gone according to plan.

Set up all the users/databases on the Mirror (DMZSQL02) and then do a back-up/restore to sync the databases and then enable the mirroring, this has all gone fine and we have lots of synchronised/mirrored databases now. However, if I do a failover the logins on the mirror are not valid, they are in SQL Server and also the database but they don’t work. I’ve read through other posts and found links to this SQL Server article:

http://support.microsoft.com/kb/918992/

This has had no effect though. Do I need to remove everything off my mirror and start again setting up the users first and then doing a backup/restore or is there some other way. Both servers are reporting the SID’s are the same for the logins which do not work. However the principle_ID is different, I'm quite sure if this is a problem or not?

Can anyone point me in the right direction of what to do next? Or have any ideas.

Thanks

Ed

I am quite sure the problem is just like your thread title orphaned logins

If you can, failover to the mirror server, and run this to list all orphaned logins in the database you want to fix (it's per-database level, not per-server-instance)

EXEC sp_change_users_login 'report'

I imagine you just have to fix them on the mirrored servers, and then failback to the primary server again after it's fixed and working on the mirror server

|||

Thanks,

I've just run that as you said and it's not showing the SQL Login I need in the database. Can you describe how I can fix/add that user?
The sp_change_users_login 'report' stored proc, does that list the users which should be there but aren't or those which are there which are supposed to be in the DB?

The SQL Login is showing in the User group for the database but not when I run the EXEC sp_change_users_login 'report' command?

Thanks
Ed

|||

Orphaned login is where the login exist at the server level, but they are not linked to the login credentials stored inside each database

I'd say first step is to open the login under Server -> Security -> pick the login -> User Mapping

if it's all blank, it's most likely orphaned. When you try to add permission on the database, it'll give you an error <- this almost guarantees it's orphaned

Then see this thread on how to auto-fix them

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615

|||

Search this forum for 'orphaned'.

|||

Hi, thanks for the help so far it’s much appreciated and very helpfull.

It appears the SP to fix the orphaned users did work; however, I now have a different problem.

The databases I am mirroring are used by our web site so in our web pages we have connection strings (and a few even use ODBC connections). In the connection strings we use an IP Address of the server (or server name) and database name/SQL Server login details.

Let’s say my principle server is 192.168.3.50 and my Mirror is 192.168.3.51 and my Witness is 192.168.3.52.

My web pages all use 192.168.3.50 as the server in their connection string, however when the database used is failed over, the active database now lives on 192.168.3.51, at this point the web page can’t find the database (as it isn’t active on the principle (192.168.3.50) any more) so reports a login failure. What I guess I need is some method for SQL server to automatically pull the data from 192.168.3.51 when 3.51 is active…. Does that make sense? So if the database on 192.168.3.50 is damaged the same server is still accessed by applications but data is pulled from 192.168.3.51 instead

If there any SQL Server mechanism I can set-up so the database continues to service requests seamlessly regardless to which server the database is currently active on?

Thanks

Ed

|||

this is off topic already, you should mark this one as answered, and create a new thread

in short, you should read more into mirroring and search a bit

other than .Net ADO which supports builtin failover server connection string, you have to handle the failover in the client side, on your own

it is beyond SQL server's scope.

We use DNS Alias so we can change the IP on the DNS without changing configurations for applications

but that means we don't have AUTOMATIC FAILOVER at all, but a manual failover with shorter down time (provided we get notified and react quickly)