ODBC Data Source Doesn't Like My SQL Query

Jul 28, 2006

Good morning all,

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?

Thanks in advance,

Chris

View 5 Replies


ADVERTISEMENT

DTSWizard Doesn't Show New ODBC Data Source?

Oct 19, 2006

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?  

View 2 Replies View Related

Opening Up Odbc Data Source In The Query Query Inside Of The Server Manager

Jun 15, 2007

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.

View 1 Replies View Related

EM DTS Designer ODBC Source As A File DSN Doesn't Work!

Jul 20, 2005

Hi All there!I am quite new in MS SQL administration so let me explain how it workon Your instances of SQL Servers.We have several DTS packages on our server, all of them managed onsome station which have seriously hardvare problem. So we wolud liketo catch two problems at one time and decided to develop systematicway of DTS manipulation.One of several aspects of this operation would be migration fromsystem ODBC data sources definitions into file ODBC sources ( .dsnfiles) in order to make them easier to manage ( backup for example,and even reusability on other workstations). All .dsn files should belocated on some network resource (\server\directory...) which wouldbe set as default ODBC directory in ODBC administrator on managementstation.When I begin to do so, then it apears that EM DTS Designer does notremember the path to the DSN files ( for example on design panel Ichose file dsn and by browse button point at the certain .dsn file,and then after DTS save the path disapears).Do You use this facility ( file .dsn) in DTS EM Designer, or maybe MShas it treated as usless, and nobody wants to use this?RegardsK

View 3 Replies View Related

Weird!! &&< Sign Doesn't Work Within DataReader Source Connecting To Mysql Using ADO.NET ODBC Option

Jul 25, 2007

my package contains

Two connections ==> one is using OLEDB connecting to SQL server 2005 and the other using ADO.NET's ODBC option to connect to mysql;



Two "Execute SQL Task" ==> one gets maximum ID(bigint) from a SQL server table and the other gets the maximum ID(unsigned) from a mysql's table and bind them to two variables ID1(string) and ID2 (int 64). http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1902297&SiteID=17&mode=1 ;



And one DataFlow whose data source is DataReader Source which queries mysql "select * from table where ID > cast(@ID1 as signed) and ID <= cast(@ID2 as signed) ". To debug, I put a DataViewer between Data Flow Source and Data Flow Destination.



Run the package, I found no data coming into DataViewer and no error message as well. However if I remove the "<=" sign and change the DataReader Source query to "select * from table where ID > cast(@ID1 as signed)", It works perfectly. Initially I thought it's variable @ID2's issue. But if I replace "<=" sign with ">=" sign in the query ==> "select * from table where ID >= (cast(@ID2 as signed) -100)", there is no problem. After tried many times, I found that query just doesn't work with "<" sign or "between". Plus the result of running package tells it's successful but just no records got inserted into Data Flow Destination.



Anyone could help? Thanks!

View 3 Replies View Related

How Do I Add An ODBC Connection Data Source As A Data Flow Source

Mar 2, 2007

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?

Thanks in advance

ADG

View 2 Replies View Related

SSIS XML Source , Doesn't Load Any Data

Jun 6, 2007





Hello !



Please, this is an urgent call; May be someone issued the sama probleb I do :



When I'm trying to import a XML file, that contains diffgram, using the XML Source task in SSIS, I choose an Inline Schema Option - everything goes well...the tables and columns are displayed, and the Import task into a Database's table succeds.



The problem is that , it doesn't load any data into the table, though there are plenty .

View 3 Replies View Related

Data Source ODBC

Dec 8, 2007

Hi all,

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.

Thanks in advance.

View 11 Replies View Related

ODBC Data Source

Jun 27, 2006

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?

View 15 Replies View Related

ODBC Data Source

May 2, 2006

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.

Thanks,
Carson

View 10 Replies View Related

ODBC Data Source

Jun 25, 2007

I am trying to use the ADO.NET bridge to connect to an old database on RISC6000 via an ODBC connection, but I receive an error.

So, I would use a script component to retrieve data directly from an ODBC connection, using a stored procedure.



Any sample code ?

Any idea ?



Thanks

View 1 Replies View Related

Truncation Of String Data With Data Reader Source Connecting To ODBC DSN

Mar 18, 2007

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:

EXEC sp_addlinkedserver
@server = 'server_name',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'odbc_dsn_name'

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.

Any assistance would be appreciated.

Thanks,

View 3 Replies View Related

ODBC Data Source Creation

Jul 18, 2001

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.

Thanks in advance,
-Marty

View 1 Replies View Related

ODBC DATA FLOW SOURCE...!

Mar 7, 2006

HI EVERYONE...!!

I WANT TO CREATE A DATA FLOW SOURCE ON ODBC TO SQL BUT A CAN'T FIND THE ODBC DATA FLOW SOURCE. SOMEBODY CAN SAY ME HOW DO THIS????

THANKS...!!!

View 3 Replies View Related

ODBC DATA FLOW SOURCE...!!!

Mar 7, 2006

HI EVERYONE...!!

I WANT TO CREATE A DATA FLOW SOURCE ON ODBC TO SQL BUT A CAN'T FIND THE ODBC DATA FLOW SOURCE. SOMEBODY CAN SAY ME HOW DO THIS????

THANKS...!!!

View 1 Replies View Related

ODBC Data Source Error

Apr 20, 2006

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?

View 7 Replies View Related

Data Source / ODBC Error

Dec 31, 2005

Hi all,

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!

Thanks in advance.

John

View 12 Replies View Related

Microsoft Exel Doesn't Show Up As A Data Source In The Import/export Wizard

Oct 12, 2007

I am trying to import an Excel file into SQL Server 2005 using the SSIS import/export wizard; however, Microsoft Excel doesn't show up in the list of the data sources. I am assuming that something else must be install from either Microsoft Office or SQL Server 2005. I am using Microsoft Office 2003 on a Window XP machine. Does anyone know what I need to do to correct this.

Thanks,
Tim

View 11 Replies View Related

Parameters Approach To Fill Report Header With Source Data Doesn't Work

Jan 19, 2007

It's well known issue, that one can't use any dataset fields in a
report header/footer directly. One of the approach is to create
query-based parameter that basically equals
=First(Fields!@FieldName@.Value, "@DataSetName@") and use that
parameter value instead. But it doesn't work in my case!



My report displays some entity description and is parametrized with
EntityID param. Its header contains entity name that, according to the
approach, is queried from the data source through the EntityName
report parameter. There's important issue: the report is displayed in
ReportViewer control, that is embedded into my application and entity
ID parameter isn't ser by user in ReportViewer parameters area. Its
default value is changed by the application with SetReportParameters()
web method every time a user wants to view the report according to the
entity the user is exploring in the application. But after the report
has been rendered, its header always contains not actual (outdated)
entity name. Nevertheless, the report body contains actual data
(including entity name). If I alter entity ID parameter in ReportViewer
or in web-based Report Manager and refresh report, header displays
correct entity name.



What's wrong in the workflow described?

View 3 Replies View Related

Setting Up ODBC Data Source For SQL Server

Jan 22, 2007

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.
Thanks,
Kumar

View 2 Replies View Related

ODBC Data Source Administrator Problem

May 25, 1999

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.


Thanks,


--Lawrence

View 1 Replies View Related

Create ODBC Data Source To SQL Server DB

Jun 3, 2002

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.

Does anybody have any issues like this?

Your input is appreciated.

View 3 Replies View Related

Setup ODBC Data Source For Access

Jul 16, 2013

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"

[code]....

View 11 Replies View Related

Automating Installation Of ODBC Data Source

Jul 20, 2005

Is there a script anyone has that will automate the addition of anaccess database to the OBDC datasources in control panel.Thanks

View 1 Replies View Related

ODBC Data Source Failed Connection

Feb 13, 2008

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?

View 2 Replies View Related

Import From An ODBC Data Source Into SQL Server

Feb 7, 2007

Hi,

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 ?

Thanks i advance.

View 1 Replies View Related

Using SSRS With An ODBC Data Source (Oracle RDB)

Feb 12, 2008

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.

Here is a snippet of my sql query:

SELECT Application_Notes.Lease_Application, Application_Notes.Followup_Code,

<...>
FROM
Application_Notes Join

<...>
WHERE
(Application_Notes.Tickler_date >= {ts '2008-02-08 00:00:00'} AND

Application_Notes.Tickler_Date < {ts '2008-02-09 00:00:00'} )


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.


Thanks,


Paul

View 6 Replies View Related

Problems Importing Data From An ODBC Source

May 3, 2006

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.

Thanks!

View 1 Replies View Related

Data Reader Source As ODBC / Complex Sql

May 30, 2007

Hello All,



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?



Thanks in advance.

View 5 Replies View Related

Data Extraction From A Source Using SOLID ODBC Driver

May 12, 2008



Hi all,

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??

Thanks and Regards,
Nirmal

View 2 Replies View Related

Using An ODBC Data Source In A Data Flow

Oct 2, 2006

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.

Thanks in advance for any info.

View 1 Replies View Related

Return Records Through A Linked Server That Uses ODBC Data Source

Jul 20, 2005

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

View 3 Replies View Related

Vista MYSQL ODBC Manager Data Source Not Found

Nov 26, 2007

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.

Does anybody know a way to solve this problem

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved