Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
entropus
Starting Member
5 Posts |
Posted - 2012-12-06 : 12:55:01
|
I have a problem with running a select from openquery.I will be extremely grateful for help! I just hit the wall, can't find a way to solve this issue.Please do not take into account field names/data types, as I had to present only a part of the whole procedure. It's quite long.I believe the problem lies in quotation marks, etc... The procedure compiles all right.Each time I execute the procedure, an error occurs:Quote:Msg 105, Level 15, State 1, Line 53Unclosed quotation mark after the character string 'SELECT TO_NUMBER(XYZ_1) XYZ_1,XYZ_2,cast (''''0'''' as number(5)) as B1, cast(''''1753-01-01'''' as date) NULL_DATEI am lost - where the heck should I put those missing quotation mark?Code:Code: [Select all] [Show/ hide]CREATE TABLE #tmpXYZ Header ( [XYZ_1] [int] PRIMARY KEY, [XYZ_2] [varchar](20), [XYZ_3] [varchar](20), -- more fields [XYZ_N] [varchar](50) )declare @sqlInv nvarchar(3000) set @sqlInv = 'insert into #tmpXYZ Header ( [XYZ_1], [XYZ_2], [XYZ_3], -- more fields [XYZ_N] ) select * FROM OPENQUERY(XYZ_ORACLE, ''SELECT TO_NUMBER(XYZ_1) XYZ_1, XYZ_2, cast (''''0'''' as number(5)) as B1, cast(''''1753-01-01'''' as date) NULL_DATE, -- more fields cast ('''' '''' as varchar(20)) as A19 from XYZ.V_HEADER where (DATE >= ''''TO_DATE(''''' + @startDate + ''''', ''''YYYYMMDD'''')'''' AND DATE <= ''''TO_DATE(''''' + @endDate + ''''', ''''YYYYMMDD'''')'''' AND QWE = ''''0'''' AND ABC = ''''13'''' ) '' ) ' exec sp_executesql @sqlInv |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-06 : 13:06:40
|
Print out the @sqlInv string and see if that makes sense. From visual observation it seems to me like you need to remove the four single quotes just before TO_DATE and the matching four single quotes just before the AND keyword..... from XYZ.V_HEADER where(DATE >= TO_DATE(''''' + @startDate + ''''', ''''YYYYMMDD'''') AND DATE <= TO_DATE(''''' + @endDate + ''''', ''''YYYYMMDD'''') AND QWE = ''''0'''' ANDABC = ''''13'''' ) '' )' Also, I assume @startDate and @endDate are character types - otherwise you will need to cast/convert them to character types (at least in SQL Server - don't know if Oracle will automatically convert). |
|
|
entropus
Starting Member
5 Posts |
Posted - 2012-12-06 : 13:11:42
|
Hey Sunitabeck, thanks for the input! Sadly, does not work, same error :/Also, I remove the 'where... ' part of the procedure. Same error. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-06 : 13:15:59
|
You don't have those comments in the actual query, do you? If you do, remove them[XYZ_3] [varchar](20),-- more fields [XYZ_N] [varchar](50) |
|
|
entropus
Starting Member
5 Posts |
Posted - 2012-12-06 : 13:16:53
|
No, no comments.(Also, I removed the 'where... ' part of the procedure. Same error.) |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-06 : 13:29:59
|
Since you are not able to post the whole query (or even if you are able to), reduce the select list to just the 3 columns you posted and see if you can make that work. If not, replace the "exec sp_executesql @sqlInv" with "print @sqlInv" and look at the query string (or post it). |
|
|
entropus
Starting Member
5 Posts |
Posted - 2012-12-06 : 13:50:28
|
Hey Sunitabeck, thanks a lot for help!I did that, and got (approximately) this result:select @ora_hrows=RET_QTY_HEADER, @ora_rrows=RET_QTY_ROWS from Openquery(XYZ2,'SELECT TO_NUMBER(RET_QTY_HEADER) RET_QTY_ROWS, TO_NUMBER(RET_QTY_ROWS) RET_QTY_ROWS FROM XYZ.STH123_LOG where NL_ID = ''3E09B4CB-1234-4123-1223-12341234''')select @ora_hrows=RET_QTY_HEADER, @ora_rrows=RET_QTY_ROWS from Openquery(XYZ2,'SELECT TO_NUMBER(RET_QTY_HEADER) RET_QTY_ROWS, TO_NUMBER(RET_QTY_ROWS) RET_QTY_ROWS FROM XYZ.STH123_LOG where NL_ID = ''3E09B4CB-1234-4123-1223-12341234''')select @ora_hrows=RET_QTY_HEADER, @ora_rrows=RET_QTY_ROWS from Openquery(XYZ2,'SELECT TO_NUMBER(RET_QTY_HEADER) RET_QTY_ROWS, TO_NUMBER(RET_QTY_ROWS) RET_QTY_ROWS FROM XYZ.STH123_LOG where NL_ID = ''3E09B4CB-1234-4123-1223-12341234''')insert into #tmpXYZ Header ([XYZ_1],[XYZ_2],[XYZ_3],[XYZ_N])select* FROM OPENQUERY(XYZ_ORACLE, ''SELECT TO_NUMBER(XYZ_1) XYZ_1,XYZ_2,cast (''''0'''' as number(5)) as B1, cast(''''1753-01-01'''' as date) NULL_DATE,cast ('''' '''' as varchar(20)) as A19 from XYZ.V_HEADER where(DATE >= TO_DATE(''20121107'',/result ends with the date |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-06 : 15:01:06
|
Are you still getting the same error? |
|
|
entropus
Starting Member
5 Posts |
Posted - 2012-12-06 : 15:29:03
|
Well there was no error while I used "print @sqlInv", but while using "exec sp_executesql @sqlInv" i get the same error.I changed the procedure so that the exec sp_executesql @sqlInv is not used, and this:'insert into #tmpXYZ Header ([XYZ_1],[XYZ_2],[XYZ_3],-- more fields[XYZ_N])select* FROM OPENQUERY(XYZ_ORACLE, ''SELECT TO_NUMBER(XYZ_1) XYZ_1,XYZ_2,cast (''''0'''' as number(5)) as B1, cast(''''1753-01-01'''' as date) NULL_DATE,-- more fieldscast ('''' '''' as varchar(20)) as A19 from XYZ.V_HEADER where(DATE >= ''''TO_DATE(''''' + @startDate + ''''', ''''YYYYMMDD'''')'''' AND DATE <= ''''TO_DATE(''''' + @endDate + ''''', ''''YYYYMMDD'''')'''' AND QWE = ''''0'''' ANDABC = ''''13'''' ) '' )' is executed directly. Sadly, I get an error: "ORA-01858: a non-numeric character was found where a numeric was expected".However, I checked all the fields and they seem to be OK regarding data types.Also, another procedure almost identical to this one is run, and works fine.No need to "- otherwise you will need to cast/convert them to character types " I believe. |
|
|
|
|
|
|
|