Post reply

Connecting to sql server from Query Analizer

  • November 6, 2004 at 4:53 am

    #66934

    Hello

    I have problems with connecting to my sql server from the query analizer using this sintax:

    SELECT *

    FROM OPENDATASOURCE('SQLOLEDB','Data Source=MyServer;User ID=sa,Password=').Bank.dbo.ClientInfo

    where Bank is the database i'm trying to connect to.

    I get this error: "Login failed for user 'sa,Password='.".

    The user name is 'sa' and i have no password so it should be ok. I am able to connect to my server from a Java program with the same user and password si i don't know why it doesn't work now.

    Should i make a server-alias in the Client Network Utility and if so, how should I configure it?

    I've tried makeing a ODBC data source but I'm not sure how to use it exactly to connect to the sql server.

    Any help is greatly appreciated

    Regards,

    Michael

  • November 7, 2004 at 9:03 am

    #529200

    Shouldn't that be server=?

  • November 7, 2004 at 9:29 am

    #529203

    Hello

    If i use "server=localhost" i get the same error:Login failed for user 'sa,Password='.

    I have resolverd the conenction problem using OPENROWSET like this:

    SELECT *

    FROM OPENROWSET('SQLOLEDB','localhost';'sa';'',

    'SELECT * FROM Bank.dbo.ClientInfo').

    My problem is tha if I try to update the table and then show the contents of that table:

    SELECT *

    FROM OPENROWSET('SQLOLEDB','localhost';'sa';'',

    'UPDATE Bank.dbo.AccountInfo

    SET amount_of_money= amount_of_money - 1000

    FROM Bank.dbo.AccountInfo,Bank.dbo.ClientInfo

    WHERE Bank.dbo.ClientInfo.idclient= 1 AND

    Bank.dbo.ClientInfo.idclient=

    Bank.dbo.AccountInfo.idclient

    SELECT * FROM Bank.dbo.AccountInfo')

    I get the followinf error:

    "Could not process object 'UPDATE Bank.dbo.AccountInfo ...OLE DB error trace [Non-interface error: OLE DB provider unable to process the objectroviderName='SQLOLEDB', Query=UPDATE Bank.dbo.AccountInfo..." and I have no idea why.

    I am new at this distributed transaction process so I would really need some help with this.

    Regards,

    Michael

  • November 8, 2004 at 2:32 am

    #529220

    First, your first post syntax is correct (I assume the comma between sa and Password is a typo, should be semi-colon). AFAIK there are only two reasons for the login error, the wrong password is supplied (or no password when one is required) or the wrong library is being used (eg named pipes instead of TCP/IP, expecially if sql server is behind firewall).

    Second, it is very difficult to do update and select in a distributed query at the same time as sql expects a select statement to return a recordset, I have achieved it once. The preferred way is to do it in separate queries

    UPDATE a

    SET a.amount_of_money = a.amount_of_money - 1000

    FROM OPENROWSET('SQLOLEDB','localhost';'sa';'',

    'SELECT amount_of_money

    FROM Bank.dbo.AccountInfo,Bank.dbo.ClientInfo

    WHERE Bank.dbo.ClientInfo.idclient = 1 AND

    Bank.dbo.ClientInfo.idclient = Bank.dbo.AccountInfo.idclient')

    SELECT *

    FROM OPENROWSET('SQLOLEDB','localhost';'sa';'',

    'SELECT * FROM Bank.dbo.AccountInfo')

    Far away is close at hand in the images of elsewhere.
    Anon.

  • November 8, 2004 at 4:18 am

    #529224

    Yes I finally realized the comma error to. I can't believe i didn't see it in the first place!

    Thank you for the supplied example to.

    Regards,

    Michael

  • November 8, 2004 at 4:43 am

    #529228

    There is one more problem for me: if I try executing all that using a transaction (begin tran UPDATE... commit tran) I get this error: MSDTC on server 'SERVER-MACHINE' is unavailable.

    I am doing all this from a single computer who has a SQL Server 2000 installed on it, so I'm trying to 'simulate' a distributed transaction.

    How do I deal with this error that I get?

    Regards,

    Michael

  • November 8, 2004 at 4:49 am

    #529231

    Make sure MSDTC (Support Services / Distributed Transaction Coordinator) is started.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • November 8, 2004 at 4:50 am

    #529232

    What is the exact error message you get?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 8, 2004 at 4:57 am

    #529233

    The Distributed Transaction Coordinator service is started and at properties in the Log on tray I have: Log on as: Local System account.

    The exact error is: "MSDTC on server 'SERVER-MACHINE' is unavailable.", where SERVER-MACHINE is the sql server name on my computer.

  • November 8, 2004 at 5:06 am

    #529234

    Could this be the case? http://support.microsoft.com/kb/822473

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 8, 2004 at 5:19 am

    #529235

    That's the problem. But if I want to fix this problem I have to obtain a Hotfix: Microsoft Windows 2000 Post-Service Pack 4 (SP4) COM+ 1.0 Hotfix Package 27 which work for Windows 2000 systems and I have Windows XP.

    With the workaround solution: "Set the stand-alone instance of SQL Server on the cluster service to restart manually." I don't know how to make this happened and I would really appreciate any help possible on this.

    Regards,

    Michael

  • November 8, 2004 at 5:27 am

    #529237

    Windows XP? SP2?

    Maybe one of these will help:

    http://www.microsoft.com/technet/prodtechnol/winxppro/maintain/sp2otech.mspx

    http://support.microsoft.com/default.aspx?scid=kb;en-us;841249

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;839279

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;841394

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;841251

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;841252

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;839269

    Good luck!

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 8, 2004 at 7:59 am

    #529273

    Thank you for the quick reply.

    I did all the things that I was supposed to do in order for this to work (followed the instructions posted on Microsoft support page) but after all that I get the same error that should be prevented by doing all the changes:

    "...New transaction cannot enlist in the specified transaction coordinator...". If Microsoft tells me how to deal with this and I still get the same error then I must conclude that distributed transaction cannot be done on the same work station (by creating a linked server, or by using the same server); it should be done in a 'real' environment on 2 servers and not trying to simulate one.

    Regards,

    Michael

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy