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.
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
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
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.
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
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.
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.
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.
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 ?
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?
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.
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).
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)
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.
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.
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.
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.
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.
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 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.
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.
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.
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.
// 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()));
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
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.
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
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!
