OPENQUERY ERROR WITH NEGATIVES NUMBERS
Apr 15, 2008
I am using a linked server to retrieve data from an off site source.
I am connecting via a Microsoft OLE DB Provider for ODBC Drivers connection to a local DSN.
I am executing a query like this in Management Studio.
SELECT * FROM OPENQUERY (MY_DSN_NAME,'SELECT * FROM MY_TABLE_NAME')
42949622.96--------> -50.00
42949660.46--------> -12.50
42949671.80--------> -1.16
42949431.65--------> -241.31
My negative numbers are coming out all messed up and in some instances it gives me an error of
Invalid data for type "numeric".
Any comments or ideas?
View 1 Replies
ADVERTISEMENT
Mar 27, 2007
I'm trying to write data to excel from an ssis component to a excel destination.
Even thought I'm writing numerics, every cell gets this error with a green tag:
Convert numbers stored as text to numbers
Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.
I'm hearing this a common problem -
On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:
<style>.text { mso-number-format:@; } </style>
is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.
Maybe some setting in my write drivers - using sql servers excel destination.
So close.. Thanks for any help or information.
View 1 Replies
View Related
Jul 20, 2005
I have a table like thisorder_id price1 -10002 20003 30004 4000I want to know the total of the order amount, I use this querySELECT SUM(price)FROM MyTableand I get 8000 instead of 9000. The negative value seems to be read twice!Do you have an idea?Thanks a lot
View 1 Replies
View Related
Nov 20, 2002
Hello,
if I am running this query and getting the error the below error
SELECT * FROM OPENQUERY(AS400ODBC,'
SELECT CBH_CREDIT_MEMO_NMBR,CBH_CREDIT_MEMO_DATE,CBH_CUST _TAX_CODE,
CBH_TAXABLE_TOTAL,CBH_CUST_NMBR FROM CREDITBLDH CR1 WHERE CR1.
CBH_CREDIT_MEMO_DATE = (SELECT MAX(CR2.CBH_CREDIT_MEMO_DATE) FROM CREDITBLDH CR2 WHERE CR1.CBH_CUST_NMBR = CR2.CBH_CUST_NMBR)')
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0666 - Estimated query processing time 7211 exceeds limit 600.]
What can I do for this?.
I can able to run fine without subquery
You suggestion appreciated.
T's,
Ravi
View 1 Replies
View Related
Sep 5, 2007
I am trying to shorten an query string I am using in an OPENQUERY, to get it less than 4k. In order to do that, I have tried to put some repeating logic into a subquery factoring clause (starting a subquery with a WITH clause). I cannot post the exact query as it has some business sensitive information, but the basic structure is
SELECT * FROM OPENQUERY( server, '
SELECT
*
FROM
(
WITH a AS
(
SELECT
a,
b,
c
FROM
table1
)
SELECT
x,
y,
z
FROM
a a1
INNER JOIN
table2 t2
ON a1.a = t2.a
)
')
When I do this, I keeping getting an error from the OLE DB provider saying 'End-of-file on communication channel'. This problem only seems to occur when I put a WITH clause in my query. Has anyone else ever had a similar problem, and has anyone found a way to deal with the problem?
View 4 Replies
View Related
Feb 1, 2007
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
View 1 Replies
View Related
Feb 26, 2007
I'm trying to create linked server to access DMX functions from SQL Server as per:
Executing prediction queries from the relational server
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx
I create the link this query
EXEC master.dbo.sp_addlinkedserver
@server = N'KLSSQL01AnalysisServerLink',
@srvproduct=N'Analysis Services 2005',
@provider=N'MSOLAP',
@datasrc=N'kls-sql01',
@catalog=N'AnalysisServicesPredictorPrototype'
GO
SELECT * FROM OPENQUERY(KLSSQL01AnalysisServerLink, 'select node_caption, node_type from [Misuse Abuse Profile].content')
where [Misue Abuse Profile] is the Mining model
Provider options: Allow in process
I receive the follwing error:
OLE DB provider "MSOLAP" for linked server "KLSSQL01AnalysisServerLink" returned message "An error was encountered in the transport layer.".
OLE DB provider "MSOLAP" for linked server "KLSSQL01AnalysisServerLink" returned message "The peer prematurely closed the connection.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "KLSSQL01AnalysisServerLink".
I found this post but there was no resolution.
run openquery(mdx) through a linked server
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=924869&SiteID=1
View 2 Replies
View Related
Feb 1, 2008
Hi All -
My Set up:
Server A - Oracle 10g Database
Server B - SQL2005
Client PC - Sql Express
Server A holds all data. I am using a linked server to connect server A and B. I use a set of stored procedures containing the openquery() syntax to get data from Server A to Server B. These stored procedures run every 20 minutes. I then create a publisher on Server B. I subscribe from the client PC to publisher to get data down from Server B to client(Download only subscription).
When I fire up the stored procedures and attempt to replicate, everything works fine. It appears after about 4-5 hours of the stored procedures running replication begins to hang more and and more until eventually it hangs for about 10 minutes and I recieve the following error:
Command attempted:
{call sp_MSreleasemakegenerationapplock}
Error messages:
The merge process was unable to create a new generation at the 'Publisher'. Troubleshoot by restarting the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200994)
I'm not sure if there is an error with openquery() locking? There is some locking activity going on with the TempDB and and Server B database. I've also come across some threads talking about the agent profiles. I'm very new to replication and very confused by all of the options in the agent profiles. Any help would be greatly appreciated!
View 1 Replies
View Related
Jul 20, 2005
Help,I am running a pass through query to oracle from SQL server 2000 asfollows;select * from openquery(nbsp, 'select * from FND_FLEX_VALUES')I have run this query through both DTS and the query analyzer and getthe foloowing error;Server: Msg 7357, Level 16, State 2, Line 3Could not process object 'select * from FND_FLEX_VALUES'. The OLE DBprovider 'MSDASQL' indicates that the object has no columns.OLE DB error trace [Non-interface error: OLE DB provider unable toprocess object, since the object has no columnsProviderName='MSDASQL',Query=select * from FND_FLEX_VALUES'].The really strange thing is, I'll get this error the first time Iexecute the query but if I execute it immeadiatley after it will runfine.Any help would be most appreciated!Cheers
View 1 Replies
View Related
Mar 31, 2006
Hello Everybody / Anybody,
Sorry but exiting problem!
The Problem: OPENQUERY throwing error [Error 7357]when the source SP uses temporary table.
Description : Need to validate data against master list. My combo on UI has a source Stored Proc(contains a temp table in it).
I'm importing data from Excel. Before import, I want to validate it against my master list values.
[say field Priority has master values "High, Medium,Low".] and in excel user has added 'ComplexHigh' under priority field]
In this case, my import validator StoredProc should not accept value 'ComplexHigh' as it is not present in my Priority master list]
I'm preparing a temp table tabName containing o/p of SP, it works fine zakkas if my SP usp_SelectData does not contain temp table.
I think you got what the situation is!! Woh!
Note : I have searched net for this and found nothing! So its challenge for all of us. TRY OUT!!
------------------------------------- The Code ----------------------------
create proc usp_SelectData
as
create table #xx (FixedCol int)
insert into #xx select 1 union select 2
select * from #xx
drop table #xx
create proc usp_SelectData2
as
create table xx (FixedCol int)
insert into xx select 1 union select 2
select * from xx
drop table xx
-- Please replace MyDB with your current Database
SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData')
-- Throws Error 7357 : [Could not process object 'EXEC MyDB.dbo.usp_SelectData'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.]
SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData2') -- Works fine
Thanks in advance...
View 5 Replies
View Related
May 13, 2008
I am running the following query trying to return server properties across a linked server. I want to store the results in a table on the server where I an running the query.
DECLARE @BuildClrVersionx nvarchar(128)
SET @BuildClrVersionx =
(SELECT *
FROM OPENQUERY(LKMSSQLXYZ01, 'CONVERT(nvarchar(128),SERVERPROPERTY("BuildClrVersion")'))
I am getting the following errors:
OLE DB provider "SQLNCLI" for linked server "LKMSSQLADM01" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
If you have any ideas how I can run this query across a linked server I would appreciate it.
Thanks,
Scott
View 8 Replies
View Related
May 25, 2006
I am using Naive Bayes, Decision Trees, and Neural Net (SSAS 2005) to predict which of two states each record belongs to.
How can I enforce a different penalty for a false positive versus a false negative ? (I am assuming that in some sense the mining algorithms can then minimize the total penalty).
View 5 Replies
View Related
Jul 20, 2005
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
View 7 Replies
View Related
Jul 20, 2005
Hi. I am searching for a reference with all error numbers an descriptionsfor ms sql odbc.Has anybody something for me?Thanks a lot.Greeting Jan Entzminger
View 1 Replies
View Related
Mar 11, 2008
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
View 6 Replies
View Related
Aug 6, 2007
I'm unable to get an alert to fire for Error 2627, Primary Key Violation. I have followed BOL's instructions for creating an alert, which appears to be nearly identical to 2000, however the alert will not work. I can see the error number in a Profiler trace along with the Severity number. I've also tried creating alerts for other error numbers and had the same result. The BOL instructions are as follows:
To create an alert using an error number
1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand SQL Server Agent
3. Right-click Alerts and then click New Alert
4. In the Name box, enter a name for this alert.
5. Check the Enable check box to enable the alert to run. By default, Enable is checked
6. In the Type box, click SQL Server event alert.
7. In the Database name list, select a database to restrict the alert to a specific database
8. Click Error number, and then type a valid error number for the alert
9. To restrict the alert to a particular character sequence, check the box corresponding to Raise alert when message contains, and enter a keyword or character string for the Message text. The maximum number of characters is 100
What am I doing wrong?
Thanks, Dave
View 4 Replies
View Related
Apr 20, 2015
My team is starting to implement error handling in our sprocs. One question we have is whether or not to use unique error numbers for custom errors (ie Errors we throw after doing some sort of validity check, not SQL Server errors). For example, we might check the value of a parameter and then throw an error that says "Parameter State_Date must be less than today, please retry".
We are using SQL Server 2012 and will be using the THROW statement, not RAISERROR, so we don't HAVE to put the numbers in sys.messages. Also, we are going to log the errors in a table, along with the error message, sproc name, line number, etc.
Is it useful to maintain a custom list of error numbers and messages? Or is it just as useful to use one standard error number and add a custom error message (which we can then search for in our code, or use the sproc name & line number we logged)? And if it is worth maintaining a list of numbers plus messages, should we go ahead and put them in sys.messages?
View 2 Replies
View Related
May 21, 2008
i have an excel sheet
in it there is a column that holds values of item numbers
some of these values are preceeded with zeros E.G "00123" (with out the ")
when i view the data in excel i get this little green corner whice states (when pressed) that these values are numbers stoerd as text.
no inside the excel sheet i have no problem with that
but when i try to import the excel sheet into ssis using an excel source task
all these values are imported as nulls!!!
i am lost
i tried converting the format of these cells to numbers but then i loose the leading zeros
what i done temporarly to solve this problem is to accept excel's suggestion and turn these values into numbers
i then import them and convert them to strings in ssis and then ad dthe zeros.
now althouge this works, this isnt realy a solution.
i canot manualy correct each excel file each time i get a new copy
and in the future i will not have the luxuery of having fixed lenght values (so i wont be able to know how many zeroe i"d need to add)
there must be a better way
please help
thanks in advance
Daniel
View 3 Replies
View Related
Feb 21, 2007
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
but don't know how. Any clues?
View 1 Replies
View Related
Sep 14, 2004
Hi All,
Does anyone know the syntax for an insert statement using Openquery in a stored procedure? All the examples I've seen are Select statements, but I want to send data to a linked server.
Would I be better off using DTS??
Thanks,
Greg
View 1 Replies
View Related
Dec 13, 2007
How to create linked server to Dbf,
How to openquery util step by step
View 1 Replies
View Related
Sep 19, 2007
HI,
Can we use OPENQUERY with a parameter? Something like this:
SELECT * FROM OPENQUERY(@SOURCE_SERVER_NAME, 'Select * from dbo.FEED')
Please let me know at the earliest. Thanks a lot,.
Mannu.
View 3 Replies
View Related
Apr 18, 2008
Hi everybody,
I am having a problem using a servername with '' in the openquery statement. I'd really appriciate if someone could suggest how I should be using it. Here is the query:
select * from openquery(sqldev est,'SELECT COUNT(*) FROM t_login WHERE username=''Tom''') into count
thanks in advance
devmetz
View 2 Replies
View Related
May 20, 2008
Hi All,I want to use the following code to use 'NOT LIKE' clause for my File system search here is the code:SELECT Docs.FileNameFROM OPENQUERY(OPINIONSERVER, 'SELECT Filename FROM SCOPE() WHERE FREETEXT(''Any text not to search'')') AS Docs I want to use the above code for my html file system search similar to:ColumnName NOT LIKE N'%1971%'The confusing part for me is that in normal queries we use the column name to search in, but while searching in the file system using the FREETEXT() function how we exclude the words user dont want to search.I am using Dotnetnuke.
View 2 Replies
View Related
Jul 20, 2001
I'm trying to use the date() function in an openquery statement in query analyzer and I keep getting an illegal symbol ")" error. The statement is :
select *
from openquery([Big Blue], 'select cde_date, cde_item from acch1 where cde_date < date()')
The objective is to find records where cde_date is prior to today. The syntax works fine if I execute it on the mainframe in QMF and the ODBC connection works fine if I hard code the value. What I need is the ability to use the function so I can run the query on subsequent days without having to edit it.
Any suggestions??
View 1 Replies
View Related
Jan 22, 2003
Hi,
I am using the below SQL query for Oracle
SELECT FP_MONTH
FROM CDWD..CDW.ACCOUNT_SALES
WHERE LAST_MODIFIED_DT > '2002-12-01 00:00:00.000'
OR SOURCE_CONTROL_DT > '2002-12-01 00:00:00.000'
My questions are
1. If I am using this date fileter it is taking long time than without filter
Why ?.
2. How I can write Open query for the above query?.
I am expecting valuable advice.
Thanks,
Ravi
View 3 Replies
View Related
Feb 5, 2003
Hi,
I need your help to solve this error.
I am running the open query against Oracle server and this shows blow.
UPDATE Lp.dbo.CB
SET oldest_invoice_date = x.oldest_invoice_date
FROM ( SELECT MIN(INVOICE_DATE) as oldest_invoice_date,
I.ACCOUNT_NUM
FROM CDWD..CDW.INVOICE I
WHERE I.INVOICE_NUM >= 0
AND INVOICE_DATE IS NOT NULL
GROUP BY (I.ACCOUNT_NUM)
)x
WHERE account_num = x.ACCOUNT_NUM
I am getting the below error
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'oldest_invoice_date'.
Anybody give solution what I am doing wrong?.
Thanks,
Ravi
View 3 Replies
View Related
Aug 25, 2004
Does anyone know how to use the openquery() method with dynamic SQL? I've tried these two different approaches with no success. DECLARE @sql nvarchar(4000)SET @sql = 'select producer_id from producer where producer_id = ' 'A' ' ' select producer_id from openquery([sybtest], @sql) -------------------------------------------DECLARE @producer_id char(1)SET @producer_id = 'A' select producer_id from openquery([sybtest], 'select producer_id from producer where producer_id = ' ' ' + @producer_id + ' ' ' ' )
View 4 Replies
View Related
Oct 11, 2005
I am trying to do the following:
SELECT ExpireDate
FROM OPENQUERY([10.22.10.79], Expire Date
From Product Where [ExpireDate] > 2005-12-31')
GO
However the above sql statement doesn't get the dates greater than the date provided unless there are quotes around the date. How do I add a variable that will cover this date and include the identifiers to get the correct records
View 4 Replies
View Related
Jan 29, 2004
Hi,
Here's what I did:
1) I declared a new VARCHAR(2000) variable called CQUERY like this:
DECLARE @CQUERY VARCHAR(2000)
2) I put a string query in the variable:
SET @CQUERY = 'SELECT ...'
Now, when I try to execute the OpenQuery method using that variable, it fails.
Here's the call:
SELECT * FROM OPENQUERY(OracleSource, @CQUERY)
I get the following error:
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '@CQUERY'.
Don't tell me I can't use a variable instead of a static query? What am I doing wrong?
Thanks,
Skip.
View 5 Replies
View Related
Jun 12, 2008
Hi,
I am trying to execute a ServerB stored Procedure which takes Int as paramter using OpenQuery in ServerA.But this doesnt seems to working.
Please Help !!!!
DECLARE @param1 INT
SET @param1 1
SELECT *
FROM OPENQUERY(ServerB,'DBNAME.dbo.SP_NAME ''@param1''')
View 3 Replies
View Related
Jul 23, 2005
In SQL Server 2000 I have set up an Oracle linked server. When I runthe following query it runs fine:-SELECT*FROMOPENQUERY(LINKEDSERVERNAME,'SELECT * FROM ORACLETABLENAME')However the following query does not work:-SELECT*FROMOPENQUERY(LINKEDSERVERNAME,'SELECT FIELDNAME FROM ORACLETABLENAME')This error is returned:-Server: Msg 7321, Level 16, State 2, Line 1An error occurred while preparing a query for execution against OLE DBprovider 'MSDAORA'.[OLE/DB provider returned message: ORA-00904: "FIELDNAME": invalididentifier]OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Preparereturned 0x80040e14].Basically select * works ok, but if I specify the field(s) I need thenit errors. I have tried entering the field names in upper and lowercase but it makes no difference.My real problem is that some dates in the Oracle database are pre 1753which SQL server does not recognise so I need to convert (decode) them.Any help would be appreciated.ThanksChris
View 2 Replies
View Related
Mar 31, 2008
Hi,
To access data from Linked server, which of the following is good Performance-wise:
1. Use of OPENQUERY to access data from Linked server
2. Using Direct query to access data using the four part Name of Linked server
Thanks in Advance.
View 1 Replies
View Related
