I am running Microsoft SQL Server 2005 Express Edition, and I am having difficulties setting up an ODBC User Data Source to connect to the SQL server.
I have tried going through "Control Panel -> Administrative Tools -> Data Sources (ODBC)" to add a user data source. For the SQL Server to connect to, I have tried specifying "localhostSQLEXPRESS", "SQLEXPRESS", "(local)", etc. -- but nothing seems to work. When the DSN configuration wizard tries to connect to the SQL Server to obtain default settings, it fails with the error "SQL Server does not exist or access denied". (Same error occurs if I try connecting to this ODBC data source later.)
But I know that the SQL Server exists and I have been using it through the SQL Server data provider in another application.
I am new to this, so any help or pointers would be greatly appreciated.
We have more than 10 databases on the same server. When I try to set up ODBC connection through my desktop to a specific database (DB1) using SQL authentication, it acts weird:
-- I give the login (testkz) access to DB1, DB1 does not show up in the default database list; I tried to type in the database name, it is not taken
-- If I give 'testkz' access to two other databases (and only these two), even though I did not give access to DB1, DB1 together with some other databases show up on the default database list. Seems to me that there are some dependency among the dbs.
I am trying to import tables from an ODBC data source into an SQL Server 2005 database. I presume that one way to achieve this is to create an Integration Services package, via Business Intelligence Studio (I already used DTS in SQL Server 2000, but not Integration Services) ?
Or is there a simpler way ? For instance, is there an import wizard that woult include an ODBC Data source ?
Hi,I used sp_addlinkedserver to link to a remote server through ODBC.When I execute select count(*) from LinkSrv.SI.DBO.SIHeader in SQL QueryAnalyzer. It returns 13705 records. But when I execute select * fromLinkSrv.SI.DBO.SIHeader. It only return 885 records. If I specify somecolumns, select ODCOMP, ODPONO, ODVDCD from LinkSrv.SI.DBO.SIHeader.It returns more records, 1213 records.I guess there is something limit the return storage, but I can notfind it.Any suggestion will be appreciated. Thank you
Here's my situation:I have an ODBC DSN setup for Timberline Data (An accounting packagethat uses pervasive.sql) on my sql box. I set up a linked server usingthe supplied timberline odbc driver. I have two sql instances setup,the default instance and a named instance. On the default instance,the linked server works great no matter who is logged in using it (allauthentication is NT integrated). However on the named instance onlythe NT account that the MSSqlserver$NAMED service is logged in undercan utilize the linked server. All others get a ODBC error:"error 7399: OLE DB Provider 'MSDASQL' reported an error. AccessDenied. OLE DB error trace [OLE/DB Provider 'MSDASQL'IUnknown::QueryInterface returned 0x80070005: Access Denied.]."Far as i can tell, both instances are setup the same, except that oneis the default and one is a named instance.Why the different results for the default instance vs a named instance.Any ideas?Thanks
I have set up a new connection as a connection from data source, but I cannot see how to use this connection to create my Data Flow Source. I have tried using an OLE DB connection, but this is painfully slow! The process of loading 10,000 rows takes 14 - 15 minutes. The same process in Access using SQL on a linked table via DSN takes 45 seconds.
Have I missed something in my set up of the OLE DB source / connection? Will a DSN source be faster?
I am new to SQL Server. I have some basic questions.
I have installed SQL Server Developer Edition on a Windows XP Media center edition. The installation is successful. Now I am trying to create an ODBC, but I there are some options which I am not quite sure about.
e.g.
1. How SQL Server verify the authenticity of the login ID? A. Windows NT.... B. SQL Server....
Client Configuration: Server alias: ??? Named Pipes: What should be the values of [pipe name]?
or should i choose other options?....
And I when I use Query Analyzer to connect to Server, the authentication fails..
Can some one point me to the right direction, or if there's some documentation on this subject, that would be very much appreciated.
I'm trying to import tables from COBOL thru Relativity client. With SQL Server 2000, I used Other (ODBC Data Source) to do this. I cannot find a comparible data source in SQL Server 2005. What do I do?
I am new to SSIS packages and want to use an ODBC data source connected to an old Btrieve set of data files. I set up the ODBC DSN, and my SQL Server Data Connection, and can browse the tables in the Server Explorer. However with regards to my SSIS package, I'm not sure whether to use an OLE DB Source or Data Reader Source. I have futzed with both of them but haven't had any luck at seeing the tables. Can someone please give me some tips.
Is there a command that can be used to set the account and password of an SSRS data source? I want to use a datasource that uses the option "(windows) credentials stored securely in the report server". The password for the account to be used can not be known by any humans (or we have to kill them after) so we want to set it programmatically. Is this possible via a command?
I'm trying to set up a user DSN for a MS Access database in Vista. In XP it's usually just a case of going to Admin Tools > Data Sources but I only get the option of SQL Server and SQL Native Client with Vista. I'm pretty sure I found a way of doing it a while ago by opening another version of Data Sources from the command prompt but can't for the life of me remember what it was.
A data reader is using a connection manager to connect to an ODBC System DSN . A query in the SqlCommand property is provided. Data is being truncated in the only string column . The data type in data reader output-->external columns shows as Unicode string [DT_WSTR] Length 7.
The truncated output in a text file is the first 3 characters from left to right . Changing the column order has no effect.
A linked server was created in SQL Server Management Studio to test the ODBC System DSN using the following:
Data returned using "OPENQUERY" does not truncate the string column indicating that the ODBC Driver returns data as expected with sql 2005, but not with the Data Reader.
I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.
Thanks in advance
Daniel Buchanan.
If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.
I'm trying to create an ODBC Data Source on both an NT 4.0 server and 2000 Professional workstation that are remote. I can not seem to get them to connect. What do I put in the server name field when creating the network library connection? I have tried both the ip address and fully qualified domain name of the sql server and neither worked.
As other contributors, all I am trying to do is import data from an ODBC source (spelled 'non-Microsoft data source') into a SQL 2005 table. I can easily do this in SQL 2000 with DTS, but when I use the same DSN in VS 2005 it doesn't work.
I created an integration project, and made a connection in Connection Manager to the DSN and clicked Test Connection. It succeeded, or so it claimed.
Click OK and drag a DataReader Source onto the Data Flow surface Doubleclick it and select the connection manager per above. Note the error: Error at Data Flow Task [DataReader Source[50]]: Cannot acquire a managed connection from the run-time connection manager.
What does that mean? More to the point, how to fix it?
My replication of those SQL 2000 servers gave errors: Data source (11): General Network Error. Check your network documentation... and ODBC (08S01): Communication link failure. The replication was across the WAN. I don't know where to start to troubleshoot this problem. Please help!
I've been working on an application that uploads an RDL to Reporting Services (through the SOAP webservice method CreateReport) programmatically. I'm having difficulty setting up the data source properties for my uploaded report. In particular the Data Source Credentials property.
The datasource for my report doesn't require credentials. By default after I upload the report to Reporting Services, the Data Source Credentials property is set to "Credentials supplied by the user running the report". How do I go about setting the Data Source Credentials property to "Credentials are not required" programmatically through the webservice?
I have been using ODBC data source administrator for years without any problem. However, after a unsuccesful MSSQL7.0 installation, i keep getting the following message:
"An error occurred while windows was working with the control panel file
D:WINNTSystem32Odbccp32.cpl"
Any insight into this would be greatly appreciated.
I am trying to setup an ODBC data source for Access. The server I'm trying to connect to is 2008 R2. I have SQL Server Management Studio installed on the computer where I'm trying to make the ODBC Data Source from. I can connect the the remote 2008 server through Management Studio, but in the ODBC wizard I do the following:
"Machine Data Source" tab, click "New" Select "User Data Source", click "Next >" Select "SQL Server", click "Next >" Click "Finish" Name: remote Description: remote Server: type the server name ie "sql.somewhere.com"
I hope this is a fairly simple problem that I'm just missing. I've installed SQL Server 2005 Developer Edition on Windows XP Professional. I'm trying to create an ODBC data source under an ID I created ("InstallOwner") that is part of the administrators group. In the Wizard, I give it a name and default to the "local" server. I selected to authenticate the ID using SQL Server authentication. However when I enter the ID and Password, I get the following error. "Connection Failed - SQL State=28000, SQL Server Error: 18456, [microsoft][SQL Native Client][SQL Server] Login Failed for user "InstallOwner". If I use the "sa" account, it works fine. Can someone point me in the right direction?
I'm attempting to use SSRS (2005) with my Oracle RDB database (old DEC RDB), using an ODBC driver. I am able to create a report, and view data, but I need to add some report parameters to filter the data. When I attempt to use parameters in the query, I either get a syntax error, or a message that my odbc connection cannot use "named" parameters, and that I should use "unnamed" parameters.
The "Tickler_Date" field is a binary date field. The above syntax is pulled from a Crystal Report, which I am considering moving over to SSRS. Ideally, I would like the user to enter a single RunDate parameter, and have the query do the following:
Where (Application_Notes.Tickler_Date >= @RunDate and Application_Notes.Tickler_Date < (@RunDate + 1 day) )
If the user has to enter two dates, that will be fine, but I can't seem to get past the errors.
I have created this query in SQL Server 2005 which uses an MS SQL Server data source - I've tested it and it perfroms as it should:
SELECT DISTINCT WIP.R0 AS [Job No], WIP_R23.R0 AS [Pack No], PlanningM2.R1 AS [Part No], WIP.R17 AS FKF, WIP2.R17 AS FKF2, SUBSTRING(PlanningM2.R0, 1, 3) AS Ref, PlanningM2.R5 AS Qty, LTRIM(RTRIM(WIP_R23.R23)) AS Shortages, LEN(PlanningM2.R1) AS StrLen, Stock.R2 AS [Stock Qty], WIP.R10 AS Status, WIP2.R10 AS [Pack Status], WIP.R15 AS [Qty UC] FROM C001_UNIDATA_WIP_NF AS WIP INNER JOIN C001_UNIDATA_PLANNINGM_NF AS PlanningM ON WIP.R0 = PlanningM.ASSY INNER JOIN C001_UNIDATA_PLANNINGM_NF AS PlanningM2 ON PlanningM.R1 = PlanningM2.ASSY INNER JOIN C001_UNIDATA_WIP_R23 AS WIP_R23 ON PlanningM.R1 = WIP_R23.R0 INNER JOIN C001_UNIDATA_WIP_NF AS WIP2 ON WIP_R23.R0 = WIP2.R0 INNER JOIN C001_UNIDATA_STOCK_NF AS Stock ON PlanningM2.R1 = Stock.R0 WHERE (WIP_R23.R23 IS NULL) AND (WIP.R0 LIKE N'%' + @RP1 + N'%') OR (WIP.R0 LIKE N'%' + @RP1 + N'%') AND (PlanningM2.R1 = RIGHT(LTRIM(RTRIM(WIP_R23.R23)), LEN(PlanningM2.R1))) ORDER BY [Job No], [Pack No]
I am now trying to recreate this query using ODBC and am having one helluva problem with it as it doesn't seem to like many of the functions I've used, such as LTRIM, RTRIM, LEN, RIGHT and won't accept the parameter I've included, or any other parameter come to think of it.
Has anyone else had a similar problem at all, and could you direct me to a solution if you have?
I'm having an odd issue importing data from an ODBC source using a DataReader Source.
There's data in the source table, and if I use a non-SSIS tool to query the source, all the data is visible. However, if I use the SSIS DataReader source, It returns no rows, and throws no error.
Any ideas for figuring this one out? This is affecting about 10% of the tables from this data source, and it's our ERP application, so there's limits on what I can do to the source schemas.
How do I get columns to output when I have a data reader source? My connection is an ODBC and does complex sql. I am connection to a Netezza database and I would like to execute a very complex query, but in essence does
Create newtable as
(select day, sessionId)
from source
// lots of other joins and unions
select day, sessionId from newtable
drop newtable
I have an ODBC connection and I have a Datareader source, I cannot connect this source to my SQL Server destination because no output columns are available. What am I missing here?
Are there any good examples of this, taking data from a ODBC source into SQL server?
I've installed SQL Express SP1 on a Windows 2003 server and I'm trying to get a new MySQL datasource to be recognized in the DTSWizard. A MySQL database needs to be moved over to a MS SQL server due to software requirements (which weren't looked at before the site was created). I've installed the ODBC Connector/MySQL software and created the System DSN (named "MySQL DB Import) in the Administrative Tools/Data Sources (ODBC) tool. When I start up the DTS wizard for SQL Express however, I do not get my new data source in the selection options for data sources. I am following the instructions on this page: http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx#ERJAE What am I missing?
We are using a package to extract data from a source build on some image using SOLID ODBC 5.2 driver. When we tried to extract data through MS SSIS(SQL Server Integration Services) package using SOLID 5.2 ODBC driver connection, it throws an error as mentioned below: ERROR DESCRIPTION:
Error: 0xC02090F5 at Data Flow Task, DataReader Source [1]: The component "DataReader Source" (1) was unable to process the data.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "DataReader Source" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Also, we tried the same in a Excel sheet using the SOLID 5.2 ODBC driver it is working fine. Does the SOLID ODBC driver support .Net framework or do we need to install any other components??
Okay, this should be really simple but I don't get it. How do I use an ODBC data source in an SSIS data flow task? When I look at Data Flow Sources I see the following options:
Pointer
DataReader Source
Excel Source
Flat File Source
OLE DB Source
Raw File Source
XML Source
Which one do I use if I need to get the data from a connection manager that is ODBC based? The IBM OLEDB driver for the AS400 doesn't work correctly so I HAVE to use an ODBC driver to connect to an AS400 data source.
I am testing an application that uses mysql that works 100% fine with Microsoft Windows XP on Windows Vista Premium Home Edition.
Using Vista's ODBC Data Source Administrator I have set up and successfuly tested the DSN Connection to the Database.
However, when I call the set up DSN with the application I get [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.
I am using the mysql-connector-odbc-3.51.12-win32.
I have tried editing the registry DSN files - the problem still persists.
