MSSQL ODBC (SQL) Connection Issue (sqlstate 2800)
May 27, 2006
I am trying to make an ODBC connection to a MSSQL 2K server on a remote machine. I am using the SQL Server driver and can see the server in the drop down list when asked which server I would like to connect to. I am using SQL authentication over TCP/IP.
The error occurs when ODBC tries to connet to SQL server to obtain the default settings. The error I receive is as follows:
Connection Failed;
sqlstate '28000';
sql server error: 18456;
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'XXXX'
I have tried setting up alternate SQL Server users with varying security rights on the server but am not able to setup the ODBC connection.
I am setting the ODBC connection up on a windows XP SP2 machine.
The remote server is running Windows server 2003 (patched upto date) and MS SQL server 2K (SP3). The connection is over a LAN and does not pass through any firewalls (hardware or software). I can create the ODBC connection without issue locally on the server.
This is my first time creating an ODBC connection to a Windows 2003 Server and I am wondering if there is some additional config I may have missed out.
Many thanks.
View 3 Replies
ADVERTISEMENT
Jan 8, 2008
Problem Restoring Backup database in MSSQL 2000.I made a backup of one of my databases from one server and now I'm trying to restore to the production server (Clustering) using Enterprise Manager. I remote the server - launched Enterprise Manager and I select the restore from disk and I give the full path of a local drive in the server where the backup is located - when I clicked "ok" it first tells me: "Unable to verify the existence of the backup file location. Do you want to use the backup file location anyway?" If I answer "Yes" and "OK" it presents a error msg: "Cannpt open backup device 'c:.......bak. Restore database terminating - Microsoft SQL-DMO(ODBC SQLSTATE 42000). I have moved this backup around and I still get the same msg. Can anybody help - need assistance ASAP - thank you
View 4 Replies
View Related
Jul 20, 2005
sql server 2000 8.00.194Microsoft Windows 2000 Service Pack 2I looked into my sqlserver agent log and found the followingODBC Error 0 Timeout expired[SQLSTATE HYT00]logon to server local '( failed)' (Job manager)I have no idea what that means but it did keep out users for a minute or more.The threads found on this forum almost all point to reccuring situationsmine is a sporadic one time issue.Is it Ok to conclude it is a network error as I did?TIAVince
View 1 Replies
View Related
Nov 15, 2007
Hi,
This is my first posting and actually I have big problem which I need to resolve immediately:
I have a 38 GB Database on my SQL Server which I want to full-backup. Until now backup was created on the same volume where the database resides. But now the volume is out of disk space. So what we did is that we connected an external USB disk drive with 500 GB to the SQL Server and try to backup there.
But our first and all consecutive backup attemps failed with error SQL-DMO (ODBC SQLState: 42000). It's working fine the first few minutes as we can see that the backup file is continously growing. But then it abruptly aborts with above error message.
Can anyone figure out why ? Any help is appreciated.
Thx
Overlord1970
View 5 Replies
View Related
Jan 18, 2008
Good morning all. I need some help with a stored proc that is driving me up a wall. It's probably something stairing me right in the face but I can't see it!
I keep getting the following error on the procedure that I'm working on:
SQL-DMO (ODBC SQLSTATE: 42000)
ERROR 156: Incorrect syntax near keyword 'AS'
Must declare the variable '@signid'
Must declare the variable '@signid'
Here's the code:
CREATE PROCEDURE [dbo].[ws_savesignature2db]
@xml as text='',
@image as image='',
@imageformat as varchar(20)='',
@imagename as varchar(40)='',
@imagesize as int=0,
AS
insert into signaturetable
([image], imageformat, imagename, imagesize)
values
(@image, @imageformat,@imagename,@imagesize)
/*
if your XML field datatype is Text or nTEXT, use the code below in Sql Server 2000.
If you use Sql Server 2005, you can use varchar(max) or nvarchar(max) which will be
much easier and you do not need the code.
*/
select @signid = @@IDENTITY
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR([xml])
FROM ws_signature
WHERE signid= @signid
if @ptrval is not null
WRITETEXT ws_signature.xml @ptrval @xml
GO
Any help you can give would be a life saver!!!!
Thanks in advance.
Jim
View 3 Replies
View Related
Jan 23, 2007
I have SQL 2000 installed in my windows 2003 server with mixed mode authentication. When I login to the server and open the SQL server manager under my windows name everything works. And if I try to create an odbc connection from one of the client pc's or from the server itself using windows authentication still everything works. Now I opened the SQL enterprise manager and in the Security section, there is a user group called BUILTINAdministrators, I was asked to deny access to this group in SQL. So I did that and added my windows login name in the security -> Login section. Now still if I try to open the enterprise manager and and login to sql under my windows login name it works. But if I try to create an odbc connection to the sql server either from the server itself or from the client work station I get the following error:
connection failed:SQLstate: '28000'SQL Server Error: 18456[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'PRODpchelin'
If I go to the security -> Login and enable BUILTAministrators group, everything works. But I would like to know how to disable that group and add my own windows group or login id in SQL server and connect using ODBC.
Your valuable feedback is greatly appriciated.
View 3 Replies
View Related
Feb 12, 2007
I have an application that connects to SQL 2000 thru ODBC.
When our administrators login on the workstation the application
works well. But when ordinary users login they get the following error.
SQLState: 28000
SQL Server Error: 18452
Login failed for user ". The user is not associated with a trusted SQL Server connection.
I have restarted SQL Server and IIS to no avail. We are using SQL and Windows auth mode.
Any ideas? This just started this morning.
View 1 Replies
View Related
Oct 9, 2006
We have an application that connects to SQL 2005 thru ODBC with the following string:
driver=SQL Server;server=server1;database=db2005;uid=serveruser;pwd=pwd1;
When our administrators login on the workstation the application works well. But when ordinary users login they get the following error. (We only have 1 domain)
SQLState: 28000
SQL Server Error: 18452
Login failed for user ". The user is not associated with a trusted SQL Server connection.
Then the standard SQL Server Login window pops up asking for the Login ID and Password. On the window the 'Use Trusted Connection' is checked and the name of the user on the workstation appears on the LoginID. What we do is uncheck the 'Use Trusted Connection' then login using the credentials above.
Need help on this one please. Thanks.
View 9 Replies
View Related
Jan 25, 2008
Hi all,
I copied the following code from MSDN2 bcp Utility:
bcp AdventureWorks.Sales.Currency2 in Currency.dat -T -c
and executed it in my C:Program FilesMicrosoft SQL Server90ToolsBinn>.
I got the following errors:
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Please help and tell me what wrong with this BCP execution or with the database, and how to make this BCP working.
Thanks,
Scott Chang
View 3 Replies
View Related
Jun 1, 2015
I am using SSIS 2014 with the below .net framework version and installed in Windows server 2012 R2 . I have installed my client's odbc drivers (both 32 bit and 64 bit) in my production server and created ODBC system DSNs for 32 bit and 64 bit.
When i open SSIS 2014 and tried to create the odbc connection but i can able to see only the 32 bit system DSN connection ,i can't able to see my 64 bit odbc system dsn connection.
Microsoft Visual Studio 2012 Shell (Integrated)
Version 11.0.50727.1 RTMREL
Microsoft .NET Framework
Version 4.5.51650
SQL Server Integration Services  Â
Microsoft SQL Server Integration Services Designer
Version 12.0.1524.0
And i installed my client odbc drivers(32,64 bit) and created ODBC system DSNs in my local system and when i open ssis 2014 and i can able to see both the ODBC system DSNS(32,64) connections from SSIS ODBC connection.
I am using below version of .net framework in my local system which was installed in windows 7 and i have SSIS 2012 also installed in my system and i can able to see both ODBC connections using 2012 as well in my local system.
Microsoft Visual Studio 2012 Shell (Integrated)
Version 11.0.50727.1 RTMREL
Microsoft .NET Framework
Version 4.5.50938
SQL Server Integration Services  Â
Microsoft SQL Server Integration Services Designer
Version 12.0.1524.0
why i can not see the ODBC 64 bit system DSN connection from SSIS in my production server ?
View 9 Replies
View Related
Feb 13, 2007
I apologize if this is not the correct forum for this posting. Looking at the descriptions, it appeared to be the best choice.
I am running Windows XP Pro SP2. I have installed the SQL Native Client for
XP. However, when I try to add a new data source through ODBC Connection
Manager, SQL Native Client is not listed as an option. I have followed this procedure on three other systems with no problems. What would be causing the
SQL Native Client to not show up in the list of available ODBC data sources?
View 4 Replies
View Related
Sep 17, 2004
We have in our applications written in VC++ access also to MSSQL but only over ODBC. Is there any performance difference (is ADO faster) between ODBC and ADO access? And if yes how much it could be ?
Thanks for your answer.
View 1 Replies
View Related
Jul 11, 2006
I work with a FoxPro programmer who wants to use an ODBC connection from his foxpro inventory database to the websites mssql inventory database to do quanity+1 updates.
But he is not sure if OBDC (or MSSQL for that matter) supports those kind of updates (field+1 instead finding out what field value is and then incrementing it).
Can one of your pros confirm or deny this?
thanks!
View 3 Replies
View Related
May 6, 2008
First off, Devshed is a great place to find information for development. I'm on here daily looking through threads, finding answeres
I'm building a backend for a program that uses msSql 2005 and ODBC .
I'm trying to insert into a table, and it is working, but i would like to find a solution to this error i get from the odbc trace:
Calc f54-5c4ENTER SQLExecute
HSTMT 00924A18
Calc f54-5c4EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 00924A18
DIAG [22018] [Microsoft][SQL Native Client]Invalid character value for cast specification (0)
when i try to insert a value of 0 (zero) into a column that is bound as a double it gives this error.
And one more thing:
if(retcode != SQL_SUCCESS | SQL_SUCESS_WITH_INFO)
shouldnt that work? (if Retcode isnot success OR success_with_info)
Any input would be greatly appreciated!
thanks
View 2 Replies
View Related
Jun 9, 2006
Hi. Is there a way in order to get the Server name using MSSQL ODBC in Visual C++? Here's my code:
CString wstr_connect ;
wstr_connect.Format( "DSN=%s;UID=%s;PWD=%s", gstr_DBName, gstr_UserID, gstr_Password ) ;
CDatabase mydb ;
BOOL ret = mydb.OpenEx( wstr_connect, CDatabase::noOdbcDialog ) ;
CString info = mydb.GetConnect( ) ;
GetConnect only returns the database name, user ID, password and WSID. How do i get the server name?
Thanks in advance!
View 4 Replies
View Related
Feb 20, 2008
Hi There,
I have developed the ODBC app which can work with Oracle as well as MSSQL server databases. I need to use transactions at some level. By setting AUTOCOMMIT OFF and using SET TRANSACTION and BEGIN WORK, COMMIT WORK everything works fine with Oracle. But with MSSQL server does not. Do I need to code something special for MSSQL? Does anyone has example on how to implement transactions with MSSQL server using ODBC?
I tried reading lots of documents on Microsoft site but nothing specifically says how to do this. One way the doc says is using SQLSetConnectAttr with SQL_ATTR_TXN_ISOLATION but does not specify how to.
Any help with appreciated. Thanks in advance.
Milind
View 4 Replies
View Related
Feb 20, 2008
Hi There,
I have developed the ODBC app which can work with Oracle as well as MSSQL server databases. I need to use transactions at some level. By setting AUTOCOMMIT OFF and using SET TRANSACTION and BEGIN WORK, COMMIT WORK everything works fine with Oracle. But with MSSQL server does not. Do I need to code something special for MSSQL? Does anyone has example on how to implement transactions with MSSQL server using ODBC?
I tried reading lots of documents on Microsoft site but nothing specifically says how to do this. One way the doc says is using SQLSetConnectAttr with SQL_ATTR_TXN_ISOLATION but does not specify how to.
Any help with appreciated. Thanks in advance.
Milind
View 3 Replies
View Related
Apr 11, 2005
Hello,
I normally use Microsoft Access to retrieve data , via ODBC connection, from a table in a SQLserver database. One of the fields in the table has a value 1 but at times when the data is retrieved into MS Access it turns out to be 100 in Access. Moreover, when I try to change it in Access my computer hangs or it takes a long time before the change is effected. It does not matter from which client Pc this action takes place it remains the same. I am using ODBC SQLserver ODBC driver version 2000.81.9042.00.
I would be grateful if you could help me out of this strange situation.
Regards,
Albert
View 4 Replies
View Related
Apr 19, 2004
Hi,
I wanted to know if there is a way to connect to my MSSQL without having to setup a datasource in the control panel.
Perferably I want to locate the database within my perl code instead. I don't want to have to setup a datasource each time I need a different database. Perhaps there is a way to pass the name of the database along with the username and password through the command line.
Any suggestions?
Thanks.
View 5 Replies
View Related
Sep 27, 2007
Hi,
I am having problems using MSSQL Express with ODBC. I am trying to retrieve a float field from the MSSQL server, but the value I get is corrupted (or at least approximated wrong). For instance, the value I want to retrieve is 59.457443, but what I actually get is 59.457442999999998. This only happens if I connect through ODBC. I did some tracing with ODBC and I've noticed that it's not a problem in my code, since the wrong value is what is actually extracted from the database. Following is an extract from the log file.
a88-ec EXIT SQLGetData with return code 0 (SQL_SUCCESS) HSTMT 03C62918 UWORD 5 SWORD 1 <SQL_C_CHAR> PTR 0x0415EB30 [ 18] "59.457442999999998" SQLLEN 50 SQLLEN * 0x0415E720 (18)
I am wondering if I overlooked something configuration-wise of if this is a bug in the driver? Any help would be greatly appreciated.
EoF
View 4 Replies
View Related
May 14, 2002
Hi, I wonder if anyone can shed some light on this issue -
We have a client/server program for MSSQL7 and MSSQL2000.
When running client/server - we have our own broker, etc,
the program ends up locking itself on SQL2000; this is not a problem on SQL7.
We noticed that it seems some system tables locking tempdb.
We also noticed that SQL2000 no longer has insert-into/bulkcopy option like SQL7. But, here we don't have temporary tables at all. It's just that we found the lockings in tempdb caused by some sysobjects, sysindexes, etc.
Also, this does not happen to all the systems we have. Some of our machine does not have the self-lock problem; most of the machines have this problem though.
If we don't run client/server, then the program works fine.
Same logic, the difference is when we use client/server logic to connect to the remote SQLServer 2000 server, the locking might happen on most of our machines.
Anyone is familiar with tempdb and locking issues, please provide some hint??? Is it possible that there are some system parameters that we can set to avoid the problem. We haven't found out why some of our machines do not have problem, while most of the machines have this locking problem.
The program is written using ODBC.
Many thanks in advance.
HIBA International
View 2 Replies
View Related
Mar 6, 2007
The source for this problem can be found http://www.wellytop.com/SQLProblem.zip
This test creates two threads each with a database connection and uses transactions to insert values into the same table.
The objective of this test is to check that a thread cannot read the results from a pending transaction on a different thread.
In effect this checks dirty reads do not happen and transaction locking.
The test runs correctly and displays "PASSED" with MySQL indicating the transaction and threading worked.
When running with MSSQL Express 2005 it reports a deadlock error during a transaction.
It's not really possible to re-run the transaction and I would like MS SQL to operate similar to MySQL, i.e. MySQL waits for the other transaction to finish before the next transaction can operate on those table rows. I'd like to use MSSQL but I am wondering why this error doesn't happen with MySQL and so have, for the moment, chosen to use it as my preferred database solution.
I have experimented with transaction isolation levels and this doesn't seem to solve the problem.
I've tested this with a fresh install of Windows XP SP2 and no firewall turned on.
To run this test with MSSQL Express2005 use the ODBC Data Source Administrator (odbcad32.exe) to create a data source named MyExpressTest and attach this to an empty database that has been created with the default values. Enable the #define MSSQL in the coude otherwise it tests with MySQL.
To run this test with MySQL (to show how this test should work) use the ODBC Data Source Administrator (odbcad32.exe) to create a data source named mySQLNewTest and attach this to an empty database that has been created with the default values. Comment out the #define MSSQL to switch to MySQL mode.
View 6 Replies
View Related
Aug 30, 2006
When I create a new odbc connection to a SQL server 2005 Db I get a failure telling me dat de SQL server does not allow remote connections.
How can I allow the server to allow this.
Any help appreciated
regards
View 1 Replies
View Related
Jul 28, 2004
Anyone support a config where you have an unix/linux (AIX here) ODBC client connection to SQL Server database? I am looking for a simple, supportable configuration that does not require me to learn perl or instal a bunch of crap gnu shareware on enterprise class machines.
Please help!
View 2 Replies
View Related
Apr 14, 2007
I lost few days to solve a problem regarding the connection to an Oracle database server (10) with goal to execute a distributed transaction.
The environment was:
Windows Server 2003 Standard
MSSQL Server 2000 Standard
Oracle 10g Production Edition
The connection was quite easy to make with the Microsoft driver, but the error was:
Server: Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].
I tried to use the oracle odbc driver, but all kinds of errors were raised, regarding the TNS name and stuff like this. The real cause was that when you create the linked server you must to check the €œAllow inbound process€? in the provider options.
The Oracle ODBC driver allow distributed transactions.
View 1 Replies
View Related
Dec 11, 2006
In my ssis package,
I have a DSN connection like this: "DSN=myDSNname". Which decide from i have to pull the data.
By using OLE DB Source Editor, I want to assign that ODBC Connection to it.
By data source Reader i can achive this but where i have to pass the hard-code SQL Query that i don't want.
i'm using the variable for dynamic SQL command.
Thanks.
Manoj
View 5 Replies
View Related
Jan 17, 2007
Greeting.
I use OdbcConnection inside clr procedure, for getting data. If I use simple EXEC dbo.clr_proc - all is OK. If I use INSERT...EXEC I recive error message: Distributed transaction enlistment failed.
I set MSDTC security options for No Authentification and Allow inbound and Allow outbound, but it's no use.
Have this problem solution? May be, I must use another method to get my data?
P.S. Linked Servers and OPENQUERY is not applicable. Sybase not describe columns in stored proc result set and one stored proc may return different result set by params.
P.S.S. Sorry for bad english.
View 1 Replies
View Related
Feb 8, 2006
Hi all,
Why doesn't my connection to ms sql server 2005 work?
Get this error...
Error connecting to server. Server says: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
I set up a DSN which is working.(tested ok anyway)
What do I need to do?
Thanks.
PHP Code:
// Configure connection parameters
$dsn = "localhost";
$username = "training";
$password = "training2";
// Connect to DB
$db_link = odbc_connect($dsn, $username, $password)
or die('Error connecting to server. Server says: '.htmlspecialchars(odbc_errormsg()));
// Select Database
mssql_select_db('training_dub') or die('Error connecting to Database. Server says: '.htmlspecialchars(odbc_errormsg()));
View 2 Replies
View Related
Sep 8, 2005
I've come from the asp world into .NET and I have looked all over the web for the proper way to make a database connection to a MSSQL server and all I have found is samples of connections to Access databases. Can anyone point me in the direction of how to create a db connection for microsoft sql server.thanks
View 1 Replies
View Related
Jul 9, 2007
I am connecting to the mssql database using ADOdb. It states that the connection was successful however, none of the query functions are working....
I get the following error:
[unixODBC][Driver Manager]Data source name not found, and no default driver specified
Any helped would be greatly appreciated.
Thanks
Code:
<?php
require_once('config.inc.php');
include('/usr/local/adodb/adodb.inc.php');
ADOLoadCode("ado_mssql");
$spevent = $_POST['spevent'];
echo $spevent."<br>";
$db = ADONewConnection('odbc_mssql');
$dsn = "Driver={SQL Server};Server=SPSERVER;Database=SPDATABASE";
$db->Connect($dsn,SPUSER,SPPWD);
if($db) {
echo "Connection Successful<br>";
$sql = "Select ID from tblEvent where ID = '26622'";
$rs = $db->Execute($sql);
if(!$rs){
echo "Bad<br>";
echo $db->ErrorMsg();
}
else{
echo "Good<br>";
}
}
else{
echo "Error in connection";
exit;
}
?>
View 2 Replies
View Related
Feb 17, 2007
I am new to MSSQL but have some experience with MySQL. Now, I am trying to connect to a recently installed MSDE 2000 SP3 on my box, using dbManager Professional, (a 3rd party manager) but all attempts to connect have failed.
The first error message when I start dbM is:
Connection failed:
SQLState: '42000'
SQL Server Error: '18456'
Login failed for user 'Administrator'
(There are no passwords assigned.)
There are successive error messages if I keep forcing the issue, with additional error indicators.
MSSQL is running and the .ini file contains these 2 lines:
BLANKSAPWD=1
SECURITYMODE=SQL
I have searched the web for solutions for all of the above messages, and implemented some logically sound advice, but these solutions, which worked for others, don't solve this problem for me.
The OS is XP Professional SP2
AMD 64 Sempron 1800+
1GB of RAM.
Any suggestion is greatly appreciated.
ineuw
View 2 Replies
View Related
Jul 20, 2005
HelloI've prepared a small aplication in VS.NET and I use SqlConnection class.Everything is ok, but when I'm trying to disconnect from server usingif (cnSQL->State != ConnectionState::Closed) cnSQL->Close()there is still connection do server present. I have to close connectionbecause I'm trying to drop this database.Have you ever heard about this kind of problem??Trociu--Plain-text over all!!!/**********/write me : trociu@autonom ict pwr wroc plsearch me : gg: 1382729
View 1 Replies
View Related
Oct 17, 2000
Has anyone had an issue with the ODBC data source that when you set it up for SQL, it does not retain the username and password you entered and keeps defaulting back to the NT windows verification instead of using the SQL verification that you choose?
I add a datasource, select validation based off of SQL profile/password assigned, enter the user and password I assigned on SQL, go through the setup and test successfully. Then I try to connect using that datasource with my VB application and it says connection failure. When I go back to the datasource, it has changed back to NT verification and removes the user/password I entered for SQL verification. Maybe there is an service pack from MS on this?
any help is appreciated. At the very least, I can just hard code into my VB app, but that is my last resort. Thanks!
View 1 Replies
View Related
