Would someone provide a code snip example of a T-SQL script using an ODCB connection against a non-SQL2K database. I can hit the outside database using DTS (so I know it is possible) but I'm having trouble with the parameters using a script.
Thanks,
Fred.
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?
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
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
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!
Hi everyone
I installed the MS SQL server 7.0 on a Windows NT 4.0 SP 4 server. The network runs with TCP/IP.
I want to connect the SQL server by a ODBC-connection. Only a connection on the server (local) was successful.
The odbcping-utility reports following:
COULD NOT CONNECT TO SQL SERVER
SQLState: 08001 Native Error: 6
Info. Message: [Microsoft][ODBC SQL Server Driver][DBNMPNTW]Selected SQL server not found.
SQLState: 01000 Native Error: 53
Info. Message: [Microsoft][ODBC SQL Server Driver][DBNMPNTW]ConnectionOpen (CreateFile()).
Do I forget to install a SQL server component? Or a network service?
Thank you for your help.
Greetings
Steven
I am currently running a application and a database server they are connected via ethernet. Only the application is connected to our network. Is there a way to connect MS Access to the database server without connecting the database server to the network? I've tried several different approaches and it still dosent work. Any help would be greatly appreciated.
Dale
Hello - I'm trying to creat an ODBC connection and the db I created is not showing up in the dropdown for 'Change the default database to:' option. I'm logged into SQL using Windows Authentication, create the db, restored it w/ a backup and then added a user w/ no problems. Any ideas?
View 1 Replies View RelatedI am trying to connect to SQL Server using a 3rd party report generating software (dbextra). Everything is located on my local box. I am trying to establish connection through ODBC. Is this installed during setup or is this a function I have to manually set up and configure?
View 2 Replies View RelatedI have front-end visual basic program that use an ODBC connection to connect with SQL server, and my questions is there any automated procedure to configure this ODBC connection instead of configure it manually specially of the user profiles was changed I have to configure it again.
Please advise
We have three users that access a particular sqlserver database via microsoft access, two of the users have no problem, the third user used to be able to use the access database but is now getting a sql server error 18452 followed by an error message 'login failed for user '(null)' reason: not associated with a trusted sql server connection. When reviewing the odbc setup that the user that is having problems has, we find two sqlserver entries when it asks 'which sqlserver do you want'. How do i go about finding out where that entry comes from so i can look into removing one of them. None of the other users have two sqlserver entries. The othere users have no problem accessing this sqlserver database via ms access, I'm trying to find out why all of a sudden this one user is having problems
View 4 Replies View RelatedFirst of all i apologise if this is in the wrong area. My problem is as follows. I had set up a couple of aspx pages on our webserver which accessed a sql database on another server. These pages were set up using frontpage 2003 and everything worked fine. A week ago the pages stopped working giving the error "Server Error in '/' Application". As far as I know nothing has been changed on the servers. On top of this frontpage has started having connection problems. Frontpage will connect to the database but is unable to access the fields giving the following error.
Server error: Unable to retrieve schema information from the record source ' NAME' in a database using the connection string:
'DRIVER={SQL Server};SERVER=servername;DATABASE=live;UID=********;PWD=********'.
The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.-------------------------------------------------------[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'NAME'.
Source: Microsoft OLE DB Provider for ODBC DriversNumber: -2147467259 (0x80004005)
I have been trying for the past few days to solve this one, but am reasonable new to SQL, so this may be an easy one.
Hope someone can help me
In my project, I have added a database called aspnetdb.mdf and I store all the required information here. Now I want to write a seperate Cryatl Report for my manager, that accesses this database and display required data. This report is not a part of the asp.net project. This is only for my manager. My database is not attached to any server. I just use the attachdb in the coonection string of my project to attach this database. no I want my crystal report to access this database. If I can create an ODBC connection to this database, it will solve my problem. Dows any body know how to access this database from Crystal with or without ODBC?
Any help is greatly appreciated.
We have a helpdesk built with Sql7 and it has an frontend built with access. We had two machines that would connect to it. Now only one machine connects and is able to see all tables. The other machine cannot see the tables now. All permissions are the same, The other operator can log into the good one and it work and log into the bad one and it not work. All connections are the same. Any ideas on the problem(s)?
View 3 Replies View RelatedHas anyone had any problems connecting to SQL server 7.0 using ODBC connection? When I create a dsn within odbc to connect to my sql server the test connection is good. But when I go into access to link the table I get an odbc error message, reading something like, the stored procedure need to complete this task could not be found on this server.
Thanks
I have a web site that provides sport news and I am using SQL Server 7.0 as my back end. The ASP page connect to SQL Server through ODBC(3.70) connection using DSN configured using TCP/IP protocol. This site has been running for
8 months with no problems.
Today, the website page was showing network errror when it was trying to get the infomation from the SQL Server. So, when tried to reconfigure the DSN using TCP/IP prtocol I got the following error.
Connection failed:
SQLState: '08001'
SQL Server Error: 11
[Microsoft][ODBC SQL Server Driver][DBMSSOCN] General Network error. Check your documentation.
Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][DBMSSOCN]ConnectionOpen(connect())
Connection failed:
SQLState: 08001
SQL Server Error: 11
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation.
Connection failed:
SQLState: 01000
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (connect())
does anyone have any idea why would this happend.
Thank You,
Piyush Patel
Dear All,
When trying to connect to the server outside the firewall through an ODBC connection string, we get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login '[database name]'. Login fails.
When trying to uncover some more information pertaining to this problem, we get the associated error number, 4060. When researching this number, we see that the number has an associated error severity code of 11. Thus Microsoft is saying that it is a problem that could be corrected at the user level. Ok, I am missing something. I give. Does anyone have a new idea or an approach on which I can begin to uncover new information? We have also checked the database login and the login for the MTSServices is in fact able to access the database through an integrated login with NT Security. Also the Domain Administrator for User Rights does not have the specific NT Group enabled to log onto the computer from the network but the 'Everyone' field is still on the server. If anyone has specific questions, email me directly. Thanks for your help in advance. Daimon
We recently install SQL Server 7 and we are testing things to get it up to speed so we can use it for our clients. We are having difficulty calling the database from a ASP page. Here is the error it gives in the browser:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''.
/asptest.asp, line 11
Now here is the ASP code:
<%
set conntemp=server.createobject("adodb.connection")
conntemp.open "DSN=Northwind;uid=dbuser;pwd=dbuser"
set rstemp=conntemp.execute("select * from customers")
howmanyfields=rstemp.fields.count -1
%>
I have created a System DSN name Northwind, and a user 'dbuser' on the PDC (running IIS as well). I have gone into SQL Server and added the user dbuser and specified to authenticate it from the PDC. What am I missing? What am I doing wrong?
Could someone please point it out to me.
TIA
-Jason
jason@flnet.com
I am using MS Access to link to an SQL table via ODBC.
The DSN was setup to "use SQL authentication" with login "username", password "pwd".
After I link to the table and try opening it, I am getting an error, after which a login screen comes up with "Use Trusted Connection" checkbox turned ON. There is the user's NT username in the login textbox. I have to check "Use Trusted Connection" OFF and enter SQL login/SQL password ("user"/"pwd")before I am able to view the table contents.
It just started happening recently. Earlier, I would get a login screen with "Use Trusted connection" OFF prompting me for my SQL username/password. And no errors, of course...
What is making the system use "Trusted connection" (and, I guess, NT authentication)???
I am explicitly asking for "SQL authentication" when creating the DSN...
That is an app ACCESS 2000 wih Named Pipe ODBC to SQL Server 6.5.
After 10 minutes without use this app, the connection closed !
Have-you idea for correct this probleme ?
Regards
Alain
One of my user is having trouble connecting to the database,i have already reset his pwd bt when she is trying to connect trhough application , he keeps getting this error
"Unable to connect to database
Sytem Error codes:[microsoft][ODBC SQL Server driver] SQL Server Log in failed for user 'Username'.
[microsoft]ODBC SQL Driver] Invalid connection string attribute
Data provider could not be initialized.
please try again or contact adminstrator!
i am sure it is not psw issue because i have reset his psw
Please help me out.
Hi,
I'm new to SQL Server, but regularly connect to Oracle via ODBC. I'm trying to a) connect to our SQL Server over an ISP and b) Run Enterprise Manager over the ISP. Both actions work fine over our campus network but fail across an ISP.
The first returns the message: Connection failed:SQLState: '01000' SQL server Error: 1326 [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen[CreateFile()]. Connection failed:SQLState:'08001' SQLServer Error 1326 [Microsoft][ODBC SQL Server Driver][Client unable to establish connection
The second returns the message: Unable to connect to server [reason:[DB-Library]Unable to connect. SQL Server is unavailable or does not exist. Register anyway.
I had previously received a different error (in case 1) and, through the knowledge Base discovered I needed MDAC drivers for named pipes. I installed them but get a new error. I can find nothing in the KB or previous SWYNK messages.
Please help..... TIA
I had an ODBC connection set up, and it has been working fine for the last week or so.
Suddenly, last night, I started getting this error and I'm at a loss for what to do.
Here is the error that I get:
I create a Test database in SQL server, also I add new user in security folder. Also I add this user in Test database, the permission are: Public, and db_owner. But when I let the user try to open the database, it is ODBC – Connection failed. I add another user a few months ago, she works fine. But the new user I added in didn’t work, did you know why. I am sure I add the same permission as the later user. Thanks.
View 2 Replies View RelatedI'm writing a VB app to read and write data to an SQL server database.
I have successfully done so using ADO however I now need to do it using ODBC.
I have set up the appropriate DSN and have no trouble reading data from the database but when I try to write to it I get a message indicating that the database is open for read only access.
I'm no sure whether the restriction is and the VB, ODBC, or database level.
Here's the code which opens the database and recordset.
Set dbsWarehouseServer = OpenDatabase(ODBCDSName, _
dbDriverNoPrompt, False, _
"DSN=" & ODBCDSName)
Set rstRailSet = dbsWarehouseServer.OpenRecordset("Select Store_date, SLN From " & TBName & " where Rail_set_ID = '" & RailID & "'", dbOpenDynaset)
Any help is greatly appreciated.
Hi all,
We recently converted an application from accecc97 to access2003.
We used to have 2 databases : 1 with all and only the data (axelD.mbd), 1 with all the forms, query's, ... (axelP.mdb)
We replaced the data (axelD.mdb) with an MSDE database, through UPSIZE and so on. That all worked fine.
This database is placed on the dataserver (DATA1), the converted programm is on the FRONT2 server (FRONT1 stil usess access97 until conversion is totally ready).
We are able to connect to the MSDE by using ADODB recordsets.
We are also perfect able to link the MSDEtables in our axelP.mbd database
We started to test some heavier parts of our application and started to get the following error:
"ERROR 3151"
"ODBC--connection to 'JOROSOFT' failed"
Nothing more.
This happened in a quite complex routine, whitch updates a lot of records, several times and so on.
We could reproduce the same error when executing the following code:
Public Function TestBestellingen()
Dim sql As String
Dim intervalset As DAO.Recordset
Dim planset As DAO.Recordset
Dim tellerke
x = Opendatabases()
tellerke = 0
sql = "Select bestelnummer from vkpbestellijn where bestelnummer > 100000"
Set planset = Db_AxelP.OpenRecordset(sql, dbOpenDynaset)
Do While Not planset.EOF
tellerke = tellerke + 1
Debug.Print tellerke & " - " & planset!Bestelnummer & " --> " & IsKlantStof(planset!Bestelnummer)
planset.MoveNext
Loop
End Function
Function IsKlantStof(p_bestelnummer)
Dim sql As String, rst As DAO.Recordset
Dim hulp_klantstof As Boolean
hulp_klantstof = False
x = Opendatabases()
sql = "select klantstof from vkpbestellijn where bestelnummer = " & p_bestelnummer
Set rst = Db_AxelP.OpenRecordset(sql, dbOpenSnapshot)
With rst
Do While Not .EOF
If !KlantStof = True Then
hulp_klantstof = True
.MoveLast
End If
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
IsKlantStof = hulp_klantstof
End Function
Function Opendatabases()
If Db_AxelP Is Nothing Then
Set Db_AxelP = DBEngine(0)(0)
End If
End Function
I know this code makes no sence in a real-time environment, but it was to reproduce the error.
This code basically runs trough a 30.000 records and does a little check for certain fields
After 1981 records we get the above error.
(sometimes after 1979 or 1980 or 1982 records)
1977 - 100859 --> True
1978 - 100860 --> True
1979 - 100860 --> False
1980 - 100861 --> True
1981 - 100861 --> True
"vkpbestellijn" is a table that is linked in the AxelP.mdb trough an ODBC connection.
We get the same error (and after 1981 times) if I change the where to a totally other range of "bestelnummer"
I have installed the latest patches of Jet 4.0, MSDE-server, ...
I've been searching the internet for abour 3 days now, and just cant even find the smallest clue what could make this happen.
I hope someone here has an idea.
If you need more details, just ask.
tia,
Axel.
P.S. I'm from Belgium, so my english aint "correct to the point".
Hi Folks,
I need to access the MSSQL data in Oracle.
For that i need to first create the data source (ODBC Connection) to the MSSQL server on UNIX. Can anybody help me in creating the ODBC connection.
Thanks,
Amit
Hi
I have EE 05 installed and need to replicated tables from an odbc connection. How would i go about doing this? I cant find a way to like import tables that i need from the odbc connection.
Thanks!
I have a MS Access 97 frontend to a SQL Server 2005 backend. I am using an ODBC User DSN to link the tables to SQL Server. The users authenticate through groups set up in Windows 2003. Some groups can open the database and do everything necessary. With other groups I get an ODBC call failed error when I try to open a table. If I try the "Get External Data", "Link Tables", from the File Menu then select ODBC and the User DSN, I don't see all of the tables on the SQL Server. For some of the groups the "Get External Data" is grayed out. I have checked all permissions on the Windows Server as well as the SQL Server and everything appears to be OK. Can anyone help me with this one.
Thanks.
Is there a way to choose which database you want to connect to in the ODBC connection?
And does this differ from one ODBC connection to another, say for instance Teradata and SQL Server?
I was looking up the syntax and it seems like you can say Database=LACPP and it will choose the database....
I have also seen username/password@LACPP.SQLSERVER
Are there any documents out there that talk about how to create different kinds of ODBC strings and if you can always limit which database you are looking at?
-Bob
I have a porblem which windows 2000 couldn't connect to SQL server 2005 through ODBC. But I have no problem with the rest of my windows XP pro. Please help. thanks
View 5 Replies View RelatedIf I create any other user (test, felix, fido, etc) in my database, that user can login successfully through Management Studio Express except not through an ODBC connection. Thus, this is a very bizarre issue. I have made sure this user has the same roles as SA. My instance is configured to handle SQL Server Authentication and Integrated. This works fine in Studio Express, but not in the ODBC connection.
When using "SA" login, I can connect to Management Studio Express and through an ODBC connection. Seems rudimentary enough, however, if I change the name in the ODBC connection i get
"Connection Failed,
SQLState '28000', SQL
Server Error 18456,
Microsoft SQL Native Client SQL Server Login failed for user 'xyz'"
Any ideas? Do I need a different ODBC Administrator?